 # Counting multiple codes as one count

• If I have a row of cells with values of Y or N in each cell, how can I bring back a value of 1 if ANY Y's exist and a value of 0 if ANY N's exist, without counting the number of Y's and N's?
Thank you

• Re: Counting multiple codes as one count

If your cells are in range A1:A10, then to return a 1 if there is at least one "Y" (and nothing if there are no "Y"s, use:

Code
``=IF(SUMPRODUCT((\$A\$1:\$A\$10="Y">0)*1),1,"")``

To return zero (or nothing) when testing for "N"s use:

Code
``=IF(SUMPRODUCT((\$A\$1:\$A\$10="N">0)*1),0,"")``

You cannot do it in one formula, of course, because both cases might be true at the same time.

• Re: Counting multiple codes as one count

That did it! Thank you so much!!

• Re: Counting multiple codes as one count

The two responses to my need (Sumproduct and Countif) both worked great! I really appreciated the help. Thank you so much.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!