Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: For Count, when count changes from cell to cell | Excel Discussion (Misc queries) | |||
Cell References | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Cell Count | New Users to Excel |