Excel: Sumproduct not working when blanks in data set

0
votes

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))
2
excel
post your SUMPRODUCT formula. - Scott Craner
added it to the original question - crams

2 Answers

2
votes

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.

0
votes

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,""))