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

Bobby Nelson

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?

Paul Newcome

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.

Paul Newcome

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.

Bobby Nelson

Thank you so much! This worked!!

Paul Newcome

Happy to help. 👍️