CODEWITHSUNDEEP
excel
crams
crams

Reputation: 325

Excel: Sumproduct not working when blanks in data set

I am trying to get a sumproduct function to average a column based on criteria in prior adjacent columns.

The column i am trying to average is calculated from a formula that has an IFERROR to return a blank if there is an error. 

=IFERROR(A5*B3,"")

some of the cells in that column containt the blank generated by the if error statement, my sumproduct is giving a #value error when it tries to average the range with the blank. I want it treated as nothing not as 0

Is there anyway around this or should I try to recreate my sumproduct using some sort of averageifs function?

This is the sumproduct in question:

=SUMPRODUCT((Data!$KM$7:$OV$7=C$7)*(Data!$KM$9:$OV$118>=$B10)*(Data!$KM$9:$OV$118<=$A10)*(Data!$BLW$9:$BLW$118))/SUMPRODUCT((Data!$KM$7:$OV$7=C$7)*(Data!$KM$9:$OV$118>=$B10)*(Data!$KM$9:$OV$118<=$A10))

Upvotes: 1

Views: 8161

Answers (3)

user20100850
user20100850

Reputation: 1

=SUMPRODUCT(P10,M10,L14,L13). This could be easy. We have to write what we want to do rather it is sum, product, division or subtract

Upvotes: 0

James ADAMS
James ADAMS

Reputation: 1

The formula =IFERROR(A5*B3,"") puts a "" in the cell. This is not a value, so formulas using this cell won't work. You need to turn it to a numeric value even though it is blank

Use =value(IFERROR(A5*B3,""))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

Try this array formula instead:

=AVERAGE(IF((Data!$KM$7:$OV$7=C$7)*(Data!$KM$9:$OV$118>=$B10)*(Data!$KM$9:$OV$118<=$A10)*(Data!$BLW$9:$BLW$118<>""),Data!$BLW$9:$BLW$118))

Being an array it needs to be confirmed with Ctrl-Shift-Enter when leaving edit mode. If done properly excel will put {} around the formula.

The reason SUMPRODUCT will not work is it tries to multiply a string, albeit an empty string but a string none the less, with numbers, which will throw an error.

The array formula ignores all the empty string cells and skips them.

Upvotes: 2

Related Questions