0

I'm trying to create a formula that looks at column A and for all the values that are the same in column A look at column B and if all of those values are the same return "Good", but if any of the Column A values has a different Value in Column B than the rest then I want the formula to return "Bad".

Here's how far I got in my formula, it works partially but not entire how I want it to: =IF(A2=A1,IF(B2=B1,"GOOD","BAD"),"")

I want the A2=A1 to be more like A2=A:A same with the B2=B1 wanting it to be more like B2=B:B, but this doesn't work due to a spill error. The best I have is getting the formula to see if the one previous is the same and compare, but when I have three that are the same the formula doesn't work as well. In that instance if three are the same in column A but in column B one of the numbers is different, only one of those is flagged BAD when the other two are fine, I want the formula to flag all three as bad if any of the numbers isn't the same in column B.

Improve this question
1
  • Can you mock up some data and expected output? It will make it easier to understand.
    – Scott Craner
    Jun 28, 2021 at 18:20

1 Answer 1

Reset to default
0

It's a little difficult to tell exactly what you are trying to do, but you could start with something like this. I have formatted the data as a Table using Ctrl+T (hence why the references refer to column names and not to range addresses).

=IF(COUNTIFS([ColumnA],[@ColumnA],[ColumnB],[@ColumnB])=COUNTIF([ColumnA],[@ColumnA]),"Good","Bad")

enter image description here

i.e. If the count of the rows that have the same value in column A as the current row and the same value in column B as the current row is the same as the count of the rows that have the same value in column A as the current row, then return 'Good', otherwise return 'Bad'.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .