Count a row if the date AND Member Number both do not match another row

I am trying to create a formula where it creates a 1 in a column where the date AND the Member Number both in one row do not match the date AND the Member Number in another row. For example, in this row, the date is 05/20/21 and the Member Number is 987654. If another row also had the date as 05/20/21 and the Member Number as 987654, then this column would show a 0. If another row did NOT have this information, then this column would show a 1 for that row. Any suggestions?

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...


    =IF(COUNTIFS([Member Number]:[Member Number], [Member Number]@row, Date:Date, Date@row) = 1, 1, 0)


    Use the COUNTIFS to count how many rows match the existing row and then say if that count is 1 (meaning only the current row meets the criteria) then we don't have a duplicate in which case we output a 1. If the COUNTIFS generates a number other than 1 (meaning at least one other row on the sheet matches) then we output a zero.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!