How do I check for duplicates within several columns and check a box

Brett Kennedy

Hi All,

I'm making a grid for staffing events. Basically each event can have up to 3 staff members. I want a formula that will check for if there are multiple events on the same date and if a staff member is accidentally double booked. If they are double booked, to check the checkbox so I can run some conditional formatting. I've attached an example spreadsheet below.

What would my formula look like to check the "Double Booked" box?

Thanks!

Brett

Jason Tarpinian

This one is ugly, but can work. You need to COUNTIFS based on three individual columns (using IF(COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Staff Name]@row) as your base forumla), so you need to compare 3 names to 3 columns individually, hence the (9) COUNTIFS below. Then you wrap that all in a IF > 3 because it will always count the name in the current row. Because if there's more than three, that means it's matching in another row.

=IF(COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Second Staff Name]:[Second Staff Name], [Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Third Staff Name]:[Third Staff Name], [Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Second Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Second Staff Name]:[Second Staff Name], [Second Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Third Staff Name]:[Third Staff Name], [Second Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Third Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Second Staff Name]:[Second Staff Name], [Third Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Third Staff Name]:[Third Staff Name], [Third Staff Name]@row) > 3, 1, 0)

Jason Tarpinian

This one is ugly, but can work. You need to COUNTIFS based on three individual columns (using IF(COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Staff Name]@row) as your base forumla), so you need to compare 3 names to 3 columns individually, hence the (9) COUNTIFS below. Then you wrap that all in a IF > 3 because it will always count the name in the current row. Because if there's more than three, that means it's matching in another row.

=IF(COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Second Staff Name]:[Second Staff Name], [Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Third Staff Name]:[Third Staff Name], [Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Second Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Second Staff Name]:[Second Staff Name], [Second Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Third Staff Name]:[Third Staff Name], [Second Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Staff Name]:[Staff Name], [Third Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Second Staff Name]:[Second Staff Name], [Third Staff Name]@row) + COUNTIFS(Date:Date, Date@row, [Third Staff Name]:[Third Staff Name], [Third Staff Name]@row) > 3, 1, 0)

Brett Kennedy

Thanks so much Jason, this clears up some things I did have to change the greater than 3 to 4 to make this work but it did!

Brett Kennedy

Jason now I got most of it to work, but how can I get that formula to ignore blank staffing cells in each column?