Complex Countifs Formula

Paul Newcome

GHustad1

So in the screenshot above, everything is working as expected except the HQ row I boxed in. The segment is the same as the other yellow rows, but as it is inactive, it shouldn't be flagged. The other yellow rows are behaving correctly (not being flagged as the HQ segment that matches is not active), just the HQ row is an issue. All other flags are firing as expected.

Paul Newcome

What about this one?

=IF([Active?]@row = 1, IF(Division@row = "HQ", IF(COUNTIFS([Segment Name]:[Segment Name], [Segment Name]@row, [Active?]:[Active?], 1) > 1, 1), IF(COUNTIFS([Segment Name]:[Segment Name], [Segment Name]@row, [Active?]:[Active?], 1, Division:Division, "HQ") > 0, 1)))

GHustad1

OMG!!!! You did it Paul. You are a super hero. Thanks to all of you for the help!!!!

Paul Newcome

Happy to help. 👍️

westbrookd

You should be able to fix that particular case by wrapping the whole formula in an IF statement that only performs the rest of the operations if the row is active. I'm not sure if there are any other cases that will show up incorrectly but the modified formula below should fix the inactive HQ rows being flagged as a duplicate.

=IF([Active?]@row, IF(Division@row <> "HQ", IF(COUNTIFS([Segment Name]:[Segment Name], [Segment Name]@row, [Active?]:[Active?], 1, Division:Division, "HQ") > 0, 1), IF(COUNTIFS([Segment Name]:[Segment Name], [Segment Name]@row, [Active?]:[Active?], 1) > 1, 1)))