SOLVED

Formula / Logic Assistance

Copper Contributor

Hi all,

 

Hoping someone might be able to help me crack a challenge I've been working on and off on for several days. So my objective is to report on what I'll call 'Good' or 'Bad' data by text or conditional formatting, I've got a partially working formula in so far as it works on an example data, and demonstrates what I'm trying to achieve simply. So where column B1 to B35 matches cell A1 AND C1 to C35 = A, I can write out 'Good' if however (and this is the key objective) if column C contains the letter 'E' in any cell in C1 to C35 then it'll return 'Bad' - so C1 to C35 is my determining factor, the matching for '20' in the examples in my screen grab is so I can get the information tied to the correct row, the below formula fails as soon as you extend the ranges for columns B and C bringing in 'different' data options - suggesting the 'AND' function is not the way to go here:

 

=IF(AND($B$1:$B$35=A1,$C$1:$C$35="A"),"Good","Bad")

 

So the challenge kicks in because in reality I'm looking at multiple different rows to match to, i.e. Column A would be say 20, 21, 22, then in column B of course there will be multiple references to 20, 21, 22 etc. then I want to return my Good or Bad text based on whether column C is all "A"s for 20 or all "A"s for 21 etc. but for any 20, 21, 22 match etc. where column C has a different reference in it, i.e. where column B = say 21 and column C  contains something else other than say "A" I want to return "Bad"

 

I've stripped this down somewhat for testing purposes. Be aware that the data and reporting sheet in the real world are on different tabs if that affects any suggestions.

 

 Bad.gifGood.gif

2 Replies
best response confirmed by Sparky1825 (Copper Contributor)
Solution

@Sparky1825

In F1:

 

=IF(A1="", "", IF(COUNTIFS($B$1:$B$10000, A1, $C$1:$C$10000, "<>A")=0, "Good", "Bad"))

 

Fill down as far as you want.

Thanks Hans, I had experimented with similar to this but clearly not quite cracked the right syntax. That'll work a treat! Good fix!
1 best response

Accepted Solutions
best response confirmed by Sparky1825 (Copper Contributor)
Solution

@Sparky1825

In F1:

 

=IF(A1="", "", IF(COUNTIFS($B$1:$B$10000, A1, $C$1:$C$10000, "<>A")=0, "Good", "Bad"))

 

Fill down as far as you want.

View solution in original post