Excel SUMPRODUCT ignore blank cells or text

0
votes

Very new to Excel. Using the following, =SUMPRODUCT(SMALL(F3:F7,{1,2,3})) to get the sum of the theee lowest cells. It works fine but gives a #Num! error if more than two cells in the range are blank or contain text. I need those cells blank or with text to be ignored and still return the sum of the three lowest numbers. Thanks

1
excel
If more than two cells are blank or contain text, then you don't have 3 numbers. The problem isn't with SUMPRODUCT, it's that there is no 3rd smallest number in that case. - BigBen

1 Answers

1
votes

use:

=SUMPRODUCT(SMALL(F3:F7,ROW($ZZ1:INDEX($ZZ:$ZZ,MIN(3,COUNT(F3:F7))))))

Depending on ones version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.