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