COUNTIF issues

[TMLandreth]

I inherited a document that counts up STI's (I work at a health clinic). they used countif statements to incorporate where the infection was detected, oral, urine, anal. and use all of those in various combinations as someone might have one, two or all three be positive. only two fields have this as the others are blood and it either is detected or not, simple enough.
I have compared the formulaes and can NOT find an difference. you are supposed to be able to enter any of the three values, in any order, or just one or two but the first formulae works, the second does NOT.

here is the working formulae:

=COUNTIF(P5:P44, "Anal") + COUNTIF(P5:P44, "Anal, Oral") + COUNTIF(P5:P44, "Anal, Urine") + COUNTIF(P5:P44, "Oral, Anal") + COUNTIF(P5:P44, "Urine, Anal") + COUNTIF(P5:P44, "Anal, Oral, Urine") + COUNTIF(P5:P44, "Oral, Anal, Urine") + COUNTIF(P5:P44, "Urine, Oral, Anal") + COUNTIF(P5:P44, "Anal, Urine, Oral") + COUNTIF(P5:P44, "Urine, Anal, Oral") + COUNTIF(P5:P44, "Oral, Urine, Anal")

and the one that works but will only allow ONE criteria to be selected despite having the same options:

=COUNTIF(O5:O44, "Anal") + COUNTIF(O5:O44, "Anal, Oral") + COUNTIF(O5:O44, "Anal, Urine") + COUNTIF(O5:O44, "Oral, Anal") + COUNTIF(O5:O44, "Urine, Anal") + COUNTIF(O5:O44, "Anal, Oral, Urine") + COUNTIF(O5:O44, "Oral, Anal, Urine") + COUNTIF(O5:O44, "Urine, Oral, Anal") + COUNTIF(O5:O44, "Anal, Urine, Oral") + COUNTIF(O5:O44, "Urine, Anal, Oral") + COUNTIF(O5:O44, "Oral, Urine, Anal")

they both offer the same options in a drop down box. The error i am getting is "this value doesn't match the data validation restrictions defined for this cell."

 

[Eric W]

Welcome to the MrExcel forum!

I can't see a difference either. It may have something to do with the number of spaces between the different words. You might try something like this:

=SUM(COUNTIF(P5:P44,{"*Anal*","*Oral*","*Urine*"}))

This should add up all the different options without having to explicitly list them all.

 

[TMLandreth]

well i found the source list table, they had them hidden, so i added the other lists of terms and it seems to work also. I was having a hard time finding it as I had never hidden a table like this.

 

[Eric W]

Glad you got it sorted.

Just as an FYI, if the source list table is in List:A1:A10 for example, you can write your formula like this:

=SUM(COUNTIF(P5:P44,List!A1:A10))

 

[TMLandreth]

Glad you got it sorted.

Just as an FYI, if the source list table is in List:A1:A10 for example, you can write your formula like this:

=SUM(COUNTIF(P5:P44,List!A1:A10))

Thanks,

The problem was i inherited this document and i could not find the tables source. not an expert in Excel, and have never hidden a sheet like that before.