TMLandreth
New Member
- Joined
- Apr 19, 2022
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
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."
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."