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!


Tags:

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!