Nested CountIFS

Jennifer Kaupke

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!

Heather Duff

Hi @Jennifer Kaupke

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

Hi @Jennifer Kaupke

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

Jennifer Kaupke

@Heather Duff Thank you soooooo much! It worked perfectly! 🤩 You are a rockstar! 🤩

https://community.smartsheet.com/discussion/comment/312701#Comment_312701

Heather Duff

@Jennifer Kaupke fantastic! Happy to help.