How can I count the number of n/a and na in a row

  • Thread starter Thread starter Bill L
  • Start date Start date
B

Bill L

I have a row with yes, no and n/a and I need to add the occurence of each.

The problem I have is that some people enter n/a and some na.

I use countif to count the number, but what function could I use to count
both n/a and na without using countif twice?

Any suggestions or help much appreciated
 
=SUMPRODUCT(--ISNUMBER(MATCH(Range,{"yes","no","n/a","na"},0)))

where Range is a vector, that is, not a multicolumn reference.
 
Thanks Aladin

an answer in about 5 minutes of my Q, and it works a treat.

Thanks, you have saved me so much time & effort.

Regards Bill
 
Bill L wrote...
I have a row with yes, no and n/a and I need to add the
occurence of each.

The problem I have is that some people enter n/a and some na.

I use countif to count the number, but what function could I use
to count both n/a and na without using countif twice?
Click to expand...
...

If your entries would only be yes, no, n/a or n/a, each possibly
including any amount of leading or trailing spaces, you could note that
each answer has a distinct vowel in it: y(E)s, n(O), n/(A) or n(A). Just
count the vowels.

yes: =COUNTIF(Range,"*e*")

no: =COUNTIF(Range,"*o*")

n/a or na: =COUNTIF(Range,"*a*")
 
Back
Top