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

=SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100)

"George" wrote:

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

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


"Joel" wrote:

=SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100)

"George" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Joel" wrote in message
...
=SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100)

"George" wrote:

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

Hmmm...

Just thought of something...

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

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))

--
Biff
Microsoft Excel MVP


"Joel" wrote in message
...
=SUMPRODUCT(--($B$16:$B$100="*Australia*"),--(NOT(ISNA($C$16:$C$100))),$C$16:$C$100)

"George" wrote:

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

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...

"George" wrote:

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

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)

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
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

Perfect - thanks T Valko!

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
Perfect - thanks T Valko!

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
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