Count if cell = a value
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 |
Count if cell = a value
=SUMPRODUCT(--(A1:A10="N"),--(B1:B10="0")) etc.
If you want to count the Ys and 0s, replace N above with Y. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Vick" wrote: 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 |
Count if cell = a value
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 -- ***** birds of the same feather flock together.. "Vick" wrote: 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 |
All times are GMT +1. The time now is 08:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com