SUMPRODUCT #N/A

[polarisking]

My range has mostly numeric values, but also includes some imputed errors such as #N/A and #DIV/0!. I'd like to be able to use SUMPRODUCT to total up only those non-error cells in the range that qualify vs. a numeric condition. For example:

Range A1:A6 equals:
4
5
#N/A
7
10
1

Range B1:B6 equals:
A
B
A
A
B
A

I'd like to, using SUMPRODUCT, total the values in Column A >=5 where the value in Column B is equal to A. But, because A3 = #N/A, SUMPRODUCT returns #N/A.

This doesn't work, but the formula would function something like

SUMPRODUCT = ( AND((ISNUMERIC(A1:A6)), (A1:A6 >= 5)) * (B1:B6 = "A"))

Answer should be 7. Anyone seen this issue before?

 

[Faseeh]

Hi PolarisKing,

This should help:

=SUMPRODUCT((IF(IF(ISNUMBER($A$1:$A$6)=TRUE,$A$1:$A$6,FALSE)>=5,IF(ISNUMBER($A$1:$A$6)=TRUE,$A$1:$A$6,FALSE),FALSE))*($B$1:$B$6="A"))

Press Ctrl+Shift+Enter to run

 

[Haseeb A]

Or, with just ENTER

=SUMPRODUCT(ISNUMBER(1/(0+A1:A6>=5))*(B1:B6="A"))

 

[polarisking]

Faseeh, nice solution. I'm hoping I can do it without processing as an array. Your solution does arrive at the correct value.

Haseeb, I love where you're going but I need to sum the values that are numeric and qualify. Your formula denotes which value(s) qualify.

 

[Haseeb A]

My mistake Polaris. I didn't see your output requirement. I thought you are trying to count. Here you go,

With just ENTER;

=SUMPRODUCT((B1:B6="A")*SUMIF(OFFSET(A1,ROW(A1:A6)-ROW(A1),),">=7"))

With Array, CTRL+SHUFT+ENTER

=SUM(IF(ISNUMBER(A1:A6)*(B1:B6="A"),IF(A1:A6>=5,A1:A6)))

 

[Haseeb A]

Also,

If you are on Excel 2007 or later could use SUMIFS

=SUMIFS(A1:A6,A1:A6,">=7",B1:B6,"A")

 

[Haseeb A]

Polaris, in my previous two posts change ">=7" to ">=5" which is your condition. Sorry for the ignorance.

 

[polarisking]

Haseeb, thank you for your varied approaches to the challenge. I particularly like the SUMIFS solution. I'm a big proponent of simplifying wherever possible and this is elegant.

I'm pondering here, but it would interesting to understand why the behavior of the SUMIF, SUMIFS, COUNTIFS, etc. vary relative to the SUMPRODUCT implementation.

 

[NARAYANK991]

Hi ,

The straightforward reason is that the SUMIF , SUMIFS , COUNTIF , COUNTIFS family ignore errors , provided the criterion ( criteria ) are framed appropriately.

For example , the following formula gives 27 as the result :

=SUMIF(A1:A6,">0")

since the SUMIF function ignores any value which is not greater than 0 ; text values and error values are both ignored or equated to 0.

However , the following formula gives #N/A as the result :

=SUMIF(A1:A6,"<>")

since here we are forcing Excel to sum any cell which is not blank ; an error value in any cell makes the overall result also an error ; a text value will however still be ignored , and return the correct result , presumably because text values are equated to 0.

SUMPRODUCT is mathematically equivalent to SUM entered using CTRL SHIFT ENTER , in that both of them carry out mathematical operations on arrays.

Thus :

=SUMPRODUCT((A1:A6)*(B1:B6="A"))

and

=SUM((A1:A6)*(B1:B6="A")) entered as an array formula , using CTRL SHIFT ENTER

will both give the same result. If you remove the error value , you get the mathematically correct result ; if not , both give a #N/A error result. If you replace the #N/A value with a text value , you get a #VALUE! error result.

So , if you wrap an ISNUMBER around the (A1:A6) part , you will get a FALSE output where there is a #N/A error value , but when you multiply using (A1:A6) , the error value trips you up.

The only way around this is to use an IF function to ensure that the error value is ignored ; but using the IF means you no longer get an array result unless you use CTRL SHIFT ENTER !

Thus , once you use an IF to exclude error values , you might as well use just SUM and array enter it using CTRL SHIFT ENTER.

Narayan