# Complex Countifs Formula

Options
2»

• ✭✭✭✭✭✭
edited 05/16/23
Options
• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!