Solved If Range Contains one of a few strings

July 19, 2017 at 14:00:24
Specs: Windows 7
I am referencing another sheet called 'Comparison'. I'm looking through K136 through K167. Cells could be either EQUAL, VARIANCE or MISSING.

Is it possible to return these below?
A value of EQUAL only if ALL cells from K136 through K167 are marked EQUAL.
A value of VARIANCE if ANY of the cells from K136 to K167 are marked VARIANCE
A value of NEEDS REVIEW if NONE of the above criteria are met (such as all missing, some missing, but not those that have variance).

I will need to extrapolate this formula over a number of other ranges, but would like it to ideally work with this one first.

I have a formula that doesn't do exactly what's needed:
=IF(COUNTIF(Comparison!$K136:$K167,"Equal"),"Submitted OK","Needs Review")

message edited by AJ600


See More: If Range Contains one of a few strings

Reply ↓  Report •

#1
July 19, 2017 at 14:51:47
✔ Best Answer
I'm a little confused by the formula that you posted. Why does it contain the string "Submitted OK" if that isn't one of the results you are looking for?

In any case, try this:

=IF(COUNTIF(K136:K157,"=EQUAL")=ROWS(K136:K157),"Equal",
IF(COUNTIF(K136:K157,"=VARIANCE")>0,"VARIANCE",
"NEEDS REVIEW"))

Breakdown:

IF(COUNTIF(K136:K157,"=EQUAL")=ROWS(K136:K157),"EQUAL"

The ROWS function counts the number of cells in the range that is referenced. If the COUNTIF function returns the same value as the number of cells in the range, then all cells must contain EQUAL. (You could hard code the number of cells in K136:K157 (22) into the formula, but the ROWS function saves you the trouble of counting, especially if you are going to be using the formula for ranges of varying sizes.)

IF(COUNTIF(K136:K157,"=VARIANCE")>0,"VARIANCE"

If the first COUNTIF is FALSE, the second COUNTIF will check for the presence of VARIANCE in at least 1 cell (">0") and return VARIANCE if TRUE.

"NEEDS REVIEW"

If both COUNTIF's are FALSE, the formula will return NEEDS REVIEW

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
July 19, 2017 at 15:04:57
Sorry for the confusion, and thank you! This is very helpful.

I placed the reference sheet key 'Comparison!' in the formula and it works seamlessly!


Reply ↓  Report •
Related Solutions


Ask Question