Count if cell = a value

[Guest]

I'm trying to find a way to count in a given set of data.
N
N 0
N 0
Y 0
Y
Y 0
N 0
For example, in the above example. I'm try to find a formula that will look
at the Y an count the number of zeros. So the answer would be 2. Or if I was
doing N it would be 3. When I try todo an array I get 7, it is countying all
values not whether it is Y or N or if it even contains a zero. Any help would
be great.

Thanks

Vick

 

[Guest]

=SUMPRODUCT(--(A1:A10="N"),--(B1:B10="0")) etc.

If you want to count the Ys and 0s, replace N above with Y.

Dave

 

[Guest]

Hello Vick,
since the data u gave beside the Y and N column is either a 0 or a blank, i
suggest to try below SUMPRODUCT formula,,

=SUMPRODUCT(--(A1:A7="Y"),(--(B1:B7=0)-(B1:B7="")))
this will count the number of Ys with a 0s beside them

=SUMPRODUCT(--(A1:A7="N"),(--(B1:B7=0)-(B1:B7="")))
this will count the number of Ns with a 0s beside them