Conditional count in range w/#N?A's

Conditional count in range w/#N?A's

Post by Frank Kabe » Wed, 14 Jul 2004 06:11:00


Hi
try
=SUMPRODUCT(--(ISNA(A1:A100)),--(B1:B100=0))

--
Regards
Frank Kabel
Frankfurt, Germany
 
 
 

Conditional count in range w/#N?A's

Post by MDAxM » Wed, 14 Jul 2004 06:20:01

Thanks! Worked like a charm.

Could you explain the "--" for me?

 
 
 

Conditional count in range w/#N?A's

Post by Domeni » Wed, 14 Jul 2004 06:21:19

Hi,

Have you tried...

=SUM(IF((ISNA(A1:A10))*(B1:B10=0),1,0))
entered using CTRL+SHIFT+ENTER

or

=SUMPRODUCT((ISNA(A1:A10))*(B1:B10=0))

In article < XXXX@XXXXX.COM >,
 
 
 

Conditional count in range w/#N?A's

Post by Norman Har » Wed, 14 Jul 2004 06:32:02

Hi!

Within the SUMPRODUCT formula given there are two implicit IF
structures that return TRUE or FALSE. The -- coerces these to 1 and 0.
Only if both structures return TRUE (1) will the result of multiplying
them equal 1 which will add 1 to the sum of the products.

For more detailed explanations do a Google search on Unary Minus.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
XXXX@XXXXX.COM