Help! Blanks being counted as zeros...fix?

[teacherniv]

i'M TRYING TO FIX THIS FORMULA SO THAT WHEN IT ADDS THE COLUMN, IT ONLY ADDS THE NUMBERS AND DOES COUNT THE BLANK CELLS. ITS COUNTING THE BLANKS AS ZEROS, AND THROWING OFF THE TOTAL. IM LOOKING TO GET THE 5 LOWEST SCORES FROM 8 POSSIBLE AND THEN ADD THEM TOGETHER.

=IF(SUM(M7:M14)>10,SUM(M7:M14)-IF(COUNTIF(M7:M14,">0")>5,(LARGE(M7:M14,1)+LARGE(M7:M14,2)+LARGE(M7:M14,3)),0))

IM PULLING OUT WHAT LITTLE HAIR I HAVE LEFT. HELP!

 

[Aladin Akyurek]

i'M TRYING TO FIX THIS FORMULA SO THAT WHEN IT ADDS THE COLUMN, IT ONLY ADDS THE NUMBERS AND DOES COUNT THE BLANK CELLS. ITS COUNTING THE BLANKS AS ZEROS, AND THROWING OFF THE TOTAL. IM LOOKING TO GET THE 5 LOWEST SCORES FROM 8 POSSIBLE AND THEN ADD THEM TOGETHER.

=IF(SUM(M7:M14)>10,SUM(M7:M14)-IF(COUNTIF(M7:M14,">0")>5,(LARGE(M7:M14,1)+LARGE(M7:M14,2)+LARGE(M7:M14,3)),0))

IM PULLING OUT WHAT LITTLE HAIR I HAVE LEFT. HELP!

Looks like...

=SUMPRODUCT(SMALL(M7:M14,ROW(INDIRECT("1:"&MIN(5,COUNT(M7:M14))))))

 

[teacherniv]

Looks like...

=SUMPRODUCT(SMALL(M7:M14,ROW(INDIRECT("1:"&MIN(5,COUNT(M7:M14))))))

THANKS! I'm not sure WHY it works if Im using a new worksheet but if using my older one, the code doesn't work. I guess I'm going to re-do the worksheet from scratch using the formula you provided. I got the worksheet from someone else and I'm thinking there are hidden formulas or conditions that I don't know about. Your formula worked. Thanks again.

 

[Aladin Akyurek]

THANKS! I'm not sure WHY it works if Im using a new worksheet but if using my older one, the code doesn't work. I guess I'm going to re-do the worksheet from scratch using the formula you provided. I got the worksheet from someone else and I'm thinking there are hidden formulas or conditions that I don't know about. Your formula worked. Thanks again.

Youare welcome.