Nested CountIFS

Hello, I am stumped!
I am trying to raise a flag when one cell is blank and 2 other cells match another row.
In the example below, the first 2 rows should not be flagged because on is G8 and one is G9 in the Core Program column. The yellow rows should be flagged because none have a deletion date and the Core Program/Program are repeated in the other rows. For the last 2 rows, I would rather have both unflagged since there is a deletion date in the one that otherwise matches but I am fine with the second row having a flag if that is too complicated. Here is the formula I was trying:
=IF(COUNTIFS([Deletion Date]:[Deletion Date], =ISBLANK([Deletion Date]@row), [Core Program]:[Core Program], =[Core Program]@row, Program:Program, =Program@row) > 1, 1, 0)
Nothing is showing up flagged with that formula.
This one gives me an "Incorrect Argument" error:
=IF([Deletion Date]:[Deletion Date] = ISBLANK([Deletion Date]@row), COUNTIFS([Core Program]:[Core Program], =[Core Program]@row, Program:Program, =Program@row) > 1, 1, 0)
Another way to explain the concept - I need to add a deletion date to the rows that already have an existing match when someone inadvertently adds a duplicate.
Thank you in advance!
Best Answer
-
Try this:
=IF(COUNT(COLLECT([Core program]:[Core program], [Core program]:[Core program], [Core program]@row, Program:Program, Program@row, [Deletion date]:[Deletion date], [Deletion date]@row, [Deletion date]:[Deletion date], "")) > 1, 1, 0)
Let me know if it works for you!
Best,
Heather
Answers
-
Try this:
=IF(COUNT(COLLECT([Core program]:[Core program], [Core program]:[Core program], [Core program]@row, Program:Program, Program@row, [Deletion date]:[Deletion date], [Deletion date]@row, [Deletion date]:[Deletion date], "")) > 1, 1, 0)
Let me know if it works for you!
Best,
Heather
-
@Heather Duff Thank you soooooo much! It worked perfectly! π€© You are a rockstar! π€©
-
@Jennifer Kaupke fantastic! Happy to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!