COUNTIF one of multiple strings?

[Scott Heath]

Can COUNTIF look for different text values in a particular string? Students
taking a quiz can choose from 4 options. Two of those options are
acceptable. They could pick "30 minutes" or "1 hour" but not "5 minutes" or
"4 hours". I'd like to tally their correct answers with something like
COUNTIF(D2150,"[30 minutes] or [1 hour]").

I suppose this could be an IF-THEN-ELSE macro, but is there a simpler
approach? Right now, I've reworded the questions so the correct answers
have distinct enough wording that they fit the same COUNTIF value (I made "1
hour" into "60 minutes" and looked for "?0 minutes", thus counting answers
with "30" or "60").

Next time, I may not be so lucky as to have such easily modified values to
track, so I'd like to know how to count up cells that hold one of a set of
strings.

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(D2150={"30 minutes","1 hour"}))

or

=SUMPRODUCT(--((D2150=B1)+(D2150=C1)>0))

where B1 holds 30 minutes and C1 holds 1 hour
for more OR options just add with a + inbetween or hard code within the
{xxx}

 

[Scott Heath]

Peo,
It works, and appears to be easy to modify! Thanks much, but do you mind
telling me how it works? Especially "--". It isn't listed as a formula
operator, what does "--" do?

 

[Peo Sjoblom]

J.E. McGimpsey has a good explanation here

http://www.mcgimpsey.com/excel/formulae/doubleneg.html