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?
Best 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

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.

Thank you so much! This worked!!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!