sumif with NAs

G

George

Hi there,
I have the following sumif function that reports an error as there are N/As
in column C. Can you please help?

=SUMIF($B$16:$B$100,"*Australia*",$C$16:$C$100)

The function works fine when there are no NAs but doesn't get there when
there are NAs in the data.
Thanks
George
 
G

George

Joel,
This still gives me N/A as the output?
I cut and pasted as exactly shown below.
George
 
S

Sheeloo

Try this
=SUMPRODUCT(--($B$16:$B$100="Australia"),--($C$16:$C$100<>"N/A"),($C$16:$C$100))

After pasting this press CTRL-SHIFT-ENTER.

Are there entries like "XXAustraliaXX"? If yes, then the above will not
work...
 
T

T. Valko

I recommend that you fix the #N/A's (if possible).

Otherwise, you'll need to use an array formula** :

=SUM(IF((ISNUMBER(SEARCH("Australia",B16:B100)))*(ISNUMBER(C16:C100)),C16:C100))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
G

George

Perfect - thanks T Valko!

T. Valko said:
I recommend that you fix the #N/A's (if possible).

Otherwise, you'll need to use an array formula** :

=SUM(IF((ISNUMBER(SEARCH("Australia",B16:B100)))*(ISNUMBER(C16:C100)),C16:C100))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
Click to expand...
 
T

T. Valko

SUMPRODUCT doesn't directly work with wildcards. You have to use something
like this:

ISNUMBER(SEARCH("Australia",range))

Also, SUMPRODUCT will never** work when #N/A's (any error) are in a numeric
array.

**You could do something like this:

=SUMPRODUCT(--(B1:B10="x"),IF(ISNUMBER(C1:C10),C1:C10))

But that would need to be array entered and if it ends up as an array
formula you're better off just using an array formula like:

=SUM(IF((B1:B10="x")*(ISNUMBER(C1:C10)),C1:C10))
 
T

T. Valko

Hmmm...

Just thought of something...
=SUMPRODUCT(--(B1:B10="x"),IF(ISNUMBER(C1:C10),C1:C10))
Click to expand...

If you're using Excel 2007 that could be written as:

=SUMPRODUCT(--(B1:B10="x"),IFERROR(C1:C10,0))

But it's still an array formula. However, I don't know if that'd be better
than the array formula:

=SUM(IF((B1:B10="x")*(ISNUMBER(C1:C10)),C1:C10))

I'd have to do some tests.
 
Top