COUNTIF for multiple values

[inno101]

Hi Guys,

I have a formula that counts the number of cells in a range that do not have a certain value in.

=(COUNTIF(C5:I5,"<>A"))

I need to expand this so that the formula gives a count for all cells within the range that either:

DO NOT have multiple values "A", "S", "T", or "H"
or
DO have values "Y" or "N"

Can anybody point me in the right direction.

Thanks in advance.
Mark

[NBVC]

Do you want the Y's and N's to be counted twice? Because they would fit both conditions.

If yes:

=SUMPRODUCT((C5:I5<>"")*ISNA(MATCH(C5:I5,{"A","S","T","H"},0))+ISNUMBER(MATCH(C5:I5,{"Y","N"},0)))

if no:

=SUMPRODUCT((C5:I5<>"")*ISNA(MATCH(C5:I5,{"A","S","T","H","Y","N"},0))+ISNUMBER(MATCH(C5:I5,{"Y","N"},0)))

Note: I also took into account not wanting to count blanks (in case there are any).

[inno101]

Hi NBVC

The answer was no...and it works a treat.

Thanks a mil!

mark.