formula to count number of two different non-numeric responses

[cookie's mom]

having trouble with this - need help on what formula to count the total of
two different non-numeric values in a range, i.e., "Yes" and "NA" - trying
COUNTIF, but having trouble...

 

[Gary''s Student]

=COUNTIF(A1:A20,"YES")
=COUNTIF(A1:A20,"NA")

 

[Ron Coderre]

Try this:

=SUM(COUNTIF(A1:A10,{"yes","na"}))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Fred Smith]

=countif(range,"Yes")+countif(range,"NA")

Regards,
Fred

 

[Michael R]

Ron,

That's a very interesting solution. I do not fully understand what the
{"yes", "na"} does and why a SUM(..) is required around the COUNTIF. Would
you mind teaching me, please?

I tried substituting the list in your formula {"yes", "na"} by a range eg
{D12} where these cells would hold the values "yes" and "na". but that
wouldn't work.

Is there a way how to apply this extended flexiility to your formula?

 

[Michael R]

Ha, I solved on epart of the challenge: ctrl-shift-enter makes the formula
work with a range

Still I am not sure about the Sum(...)

 

[Dave Peterson]

Highlight the characters COUNTIF(A1:A10,{"yes","na"}) in the formula bar and hit
F9.

You'll see something like
{2,4}

It's an array of values.

You're using =sum() to add them up.

 

[Dave Peterson]

ps. Hit escape after you've see the array.

Or hit Ctrl-z (edit|undo) to get the formula back to what it was.

 

[Michael R]

Very interesting & useful!, thank you

ps. Hit escape after you've see the array.

Or hit Ctrl-z (edit|undo) to get the formula back to what it was.