Complex Countifs Formula

GHustad1

I have a sheet created where I need to 'flag' rows that are in conflict based on a number of criteria:

1. Is the 'Active' column (which is also a flag) checked?
2. Does the value in the 'Segment Name' column appear on other rows of the sheet where the 'Division' column = 'HQ'?

I have the formula below which works EXCEPT it is comparing all the values in the 'Segment Name' column to each other instead of only looking to see if the value on that row is a duplicate AND the 'Division' column = 'HQ'.

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

Any thoughts?

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)))

Kelly Moore

@GHustad1

Try this

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

As written the COUNTIFS will display the same value for every row that meets the above critiera. That is, if there are three values in the entire column that meet the criteria, the first row meeting the criteria will display 3, the second instance will display 3 as well the third instance. If you want the counts incremented, 1, 2, 3 then another criteria would have to be added to help keep track of how far down the sheet the row is. Let me know if you need the incremental counting.

Will the formula above work for you?
Kelly

GHustad1

That doesn't seem to work, as I'm getting no 'flags' for duplicate values and I should be. I think that formula isn't taking into account the complexity needed maybe? Hopefully this table will help provide more context. Effectively, any Division is allowed to have duplicate segments with any Division other than 'HQ'. HQ also cannot have duplicates with itself. There can be duplicate segments between Divisions and HQ provided either the Division or HQ is not flagged as 'Active'.

Paul Newcome

Possibly an IF/AND with two COUNTIFS. If there is at least one segment/active/"HQ" AND there is more than one segment/active?

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

GHustad1

Paul! That is so close! It seems to be working for all duplicate issues EXCEPT it is flagging any HQ instance as a duplicate. Meaning, if I have a row that has a segment name that doesn't appear anywhere in the sheet except a single HQ row, that's still being flagged, and it should only be flagged IF there is another HQ active row OR an active row for any other division.

Paul Newcome

Are you able to provide a screenshot with the updated formula? The first COUNTIFS from Kelly should be keeping that from happening.

GHustad1

I just used your formula, so it is currently:

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

Paul Newcome

EDIT: The below was my original response, but the more I thought about it, the more I realized it may not work as expected. Let me fiddle with it some more or hope that Kelly can beat me to it.

Original (incorrect) comment:

Ah. There's the problem. You forgot the "greater than 1" piece after the first COUNTIFS.

That means the COUNTIFS is outputting a 1 which is also considered "true" (think of it as a checkbox kind of thing).

Since it is outputting a 1/true AND the second COUNTIFS is greater than zero (also true), both pieces of the AND function are outputting "true" which makes the whole AND statement "true" which is checking the box.

Adding in that "greater than 1" piece after that first COUNTIFS will mean that portion of the AND function will be "false" until there is more than one row that meets the segment/active/HQ requirements.

GHustad1

Yeah, adding that piece in seemed to wipe out all "flags".

GHustad1

I stripped it down a bit to only this: =IF(COUNTIFS([Segment Name]:[Segment Name], [Segment Name]@row, [Active?]:[Active?], =1, Division:Division, "HQ") > 0, 1) and it still seems to be working the same way that the more complicated formula was working…but it is still flagging single HQ rows as duplicates.

GHustad1

Do I need an and statement back in there that somehow looks for HQ rows only, but then with a >1 instead of >0?

Paul Newcome

Lets try this:

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

GHustad1

This is seriously the most frustrating formula ever. I'm starting to wonder if I need to do it in two parts and then join them in a 3rd column to make it happen. The new formula you provided is no longer flagging the non-duplicated HQ rows (yeah), but it is also no longer flagging the HQ rows that are in conflict with other divisions. So for example where I have West in conflict with HQ, the West row IS flagged as expected, but the HQ row is not flagged.

westbrookd

It seems like there is a different set of criteria to evaluate depending on if a row is in the "HQ" division or one of the regional divisions. Since that is the case the most straightforward way to me to accomplish this is to just do a different calculation for each case like this:

=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))

The logic is: "If the row is not in the HQ division, flag the row if any other active rows exist with the same segment name in the HQ division. If the row is in the HQ division, flag the row if any other active rows exist with the same segment name."

I tested this on the sample table you provided and it seemed to provide the expected output.

GHustad1

First off, this community is amazing and I'm so thankful there are people willing to help. Second, this is soooo close! The only remaining issue seems to be that HQ rows are being flagged as duplicates on the HQ row only when there are other divisions with the same segment name but the HQ row is inactive. So to restate another way, I have a Segment 1 row that IS active for Central, a Segment 1 row that IS active for East, and then a Segment 1 row that IS NOT active for HQ. The Central and East rows are not being flagged, which is correct, however the HQ row is being flagged, which is not correct.

Paul Newcome

Are you able to provide a screenshot of more variations (at least all that should be checked and a few that shouldn't)?