Solved If multiple ranges contain a certain text, then return text.

September 25, 2017 at 01:51:37
Specs: Windows 7
Works for one range as in
=IF(COUNTIF(range,"Pass"),"Pass","Fail")

But not for more than one range as in
=IF(COUNTIFs(range1,"Pass", range2,"Pass"),"Pass","Fail")
...this still returns a Fail even if range2 has a Pass.


See More: If multiple ranges contain a certain text, then return text.

Reply ↓  Report •

#1
September 25, 2017 at 04:52:43
✔ Best Answer
You can use the COUNTIFS function to count cells:
In a single range with a single condition, ( like COUNTIF() )
Or in multiple ranges with multiple conditions.
If using Multiple ranges, only those cells that meet all of the specified conditions are counted.
In other words, COUNTIFS() "ands" each criteria, so it reads:

COUNTIFS(range1,criteria1 "and" range2,criteria2 "and" range3,criteria3 etc etc )

I think your looking for something like:

=IF(OR(COUNTIF(range1,"Pass"),COUNTIF(range2,"Pass")),"Pass","Fail")

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
September 25, 2017 at 06:45:06
Another way to say what Mike said...

COUNTIFS requires that the corresponding cell(s) in all ranges meet the criteria in order to be counted.

Examples:

If range1 is A1:A3 and range2 is B1:B3, then, for example, A2 and B2 must meet the criteria in order to be counted. (the second cell in each Range)

If range1 is A1:A3 and range2 is B4:B6, then, for example, A2 and B5 must meet the criteria. (Again, the second cell in each Range)

If all you are trying to do is see if a Pass exists at least once anywhere within all ranges, you could combine all ranges into a single "big range" and use something like:

=IF(COUNTIF(bigRange,"Pass"),"Pass","Fail")

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

message edited by DerbyDad03


Reply ↓  Report •

#3
September 25, 2017 at 07:14:39
@DerbyDad: Your explanation on why my formula does not work explains everything. But I can't use big range as I need ONE pass within each range to return an overall pass.

@Mike: Your solution works when I change the OR to AND!
As in: =IF(AND(COUNTIF(range1,"Pass"),COUNTIF(range2,"Pass")),"Pass","Fail")

It works perfectly!!!! if someone fails and passes on a second try (2nd cell in the range), an overall pass is returned if the other ranges also have a pass in each.

Thanks so much for the quick response. I was tearing my hair out.

message edited by DavidChin


Reply ↓  Report •

Related Solutions

#4
September 25, 2017 at 07:32:07
One more follow on problem:
=IF(AND(COUNTIF(range1,"Pass"),COUNTIF(range2,"Pass")),"Pass","Fail")
But if any of the cells in each range has "NE", then I want to return the text "Not Eligible". I have done this with Nested IFs before but I can't get it to work here.

Reply ↓  Report •

#5
September 25, 2017 at 08:09:22
SOLVED! I did a nest IF with a COUNTIFS

=IF(AND(COUNTIF(range1,"Pass"),COUNTIF(range2,"Pass")),"Pass",IF(COUNTIFS(range1,"NE",range2,"NE"),"Not Eligible","Fail"))


Reply ↓  Report •

Ask Question