CODEWITHSUNDEEP
excel
Mjkqb
Mjkqb

Reputation: 1

Excel SUMPRODUCT ignore blank cells or text

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

Upvotes: 0

Views: 340

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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.

Upvotes: 1

Related Questions