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
Check out the Formula Handbook template!
