SUMIF using #N/A error as criteria
 

Is it possible to use the #N/A error as criteria when using SUMIF?

I'm thinking of something along the lines of
=SUMIF(B3:B32,ISNA(C3:C32)), either entered as an array formula or as
a conventional formula, however, I keep getting 0 returned. The value
should be greater than 0.

(And, yes, the #N/A values in C3:C32 are logical values returned as
the result of a formula; they are not hard-coded in.)

Thanks.

Answer: SUMIF using #N/A error as criteria
 

Yes, it is possible to use the #N/A error as criteria when using SUMIF. The formula you provided, =SUMIF(B3:B32,ISNA(C3:C32)), is on the right track but needs a slight adjustment.

Here's the correct formula: =SUMIF(C3:C32,"#N/A",B3:B32)

This formula will sum the values in B3:B32 only if the corresponding cell in C3:C32 contains the #N/A error.

Here's how it works:

1. The first argument, C3:C32, is the range of cells that will be checked for the #N/A error.
2. The second argument, "#N/A", is the criteria that the formula will look for in the range specified in the first argument.
3. The third argument, B3:B32, is the range of cells that will be summed if the criteria in the second argument is met.

Make sure that the cells in the range C3:C32 actually contain the #N/A error and not just the text "#N/A". You can check this by selecting one of the cells and looking at the formula bar. If it shows #N/A, then the cell contains the error. If it shows "#N/A", then it's just text.

SUMIF using #N/A error as criteria
 

Try it like this:

=SUMIF(C3:C32,"#N/A",B3:B32)


--
Biff
Microsoft Excel MVP


"Dave F" wrote in message
...
Is it possible to use the #N/A error as criteria when using SUMIF?

I'm thinking of something along the lines of
=SUMIF(B3:B32,ISNA(C3:C32)), either entered as an array formula or as
a conventional formula, however, I keep getting 0 returned. The value
should be greater than 0.

(And, yes, the #N/A values in C3:C32 are logical values returned as
the result of a formula; they are not hard-coded in.)

Thanks.

SUMIF using #N/A error as criteria
 

These work:

=SUMIF(C3:C32,#N/A,B3:B32)

=SUMIF(C3:C32,NA(),B3:B32)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Dave F" wrote in message
...
Is it possible to use the #N/A error as criteria when using SUMIF?

I'm thinking of something along the lines of
=SUMIF(B3:B32,ISNA(C3:C32)), either entered as an array formula or as
a conventional formula, however, I keep getting 0 returned. The value
should be greater than 0.

(And, yes, the #N/A values in C3:C32 are logical values returned as
the result of a formula; they are not hard-coded in.)

Thanks.

SUMIF using #N/A error as criteria
 

One possible way:

=SUMPRODUCT(--(ISNA(C3:C32)),B3:B32)

HTH,
Paul

--

"Dave F" wrote in message
...
Is it possible to use the #N/A error as criteria when using SUMIF?

I'm thinking of something along the lines of
=SUMIF(B3:B32,ISNA(C3:C32)), either entered as an array formula or as
a conventional formula, however, I keep getting 0 returned. The value
should be greater than 0.

(And, yes, the #N/A values in C3:C32 are logical values returned as
the result of a formula; they are not hard-coded in.)

Thanks.