How to use COUNTIF but exclude duplicates?

Aaron Kennedy

I want to count the number of jobs for each month of the year but exclude the duplicates, by duplicates I mean jobs with the same number on the same date, I only want to count them once for that day. My two columns are "PM" (the job number) and "Date". Also in these posts what does @cell mean? I see people use it a lot.

If there is also a way to do this in reports that would help too, some way to sort and count on a report but exclude duplicates which I'm not sure you can do.

Thanks

Paul Newcome

I would suggest a helper column on the source sheet with this column formula:

=1 / COUNTIFS([Job Number]:[Job Number], @cell = [Job Number]@row, Date:Date, @cell = Date@row)

For instances where there is only one row with that job number/date column, it should output 1. If there are two rows with the same job number/date, there should be 0.5 on both rows. Basically, if you add up all rows that share a job/date combo, it should add up to 1.

Then in the sheet with the formula, you can use a SUMIFS on this column along the lines of

=SUMIFS({Helper Column}, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

Paul Newcome

Are all of the jobs on the same sheet, or do you have multiple sheets where the jobs could be duplicated?

Aaron Kennedy

To: Paul

I'm using a new sheet to count them, I'm pulling my data from 1 other sheet which has all my jobs listed, and I'm counting them to then put them on a dashboard

Full list sheet —> New sheet to count —> Dashboard

Paul Newcome

I would suggest a helper column on the source sheet with this column formula:

=1 / COUNTIFS([Job Number]:[Job Number], @cell = [Job Number]@row, Date:Date, @cell = Date@row)

For instances where there is only one row with that job number/date column, it should output 1. If there are two rows with the same job number/date, there should be 0.5 on both rows. Basically, if you add up all rows that share a job/date combo, it should add up to 1.

Then in the sheet with the formula, you can use a SUMIFS on this column along the lines of

=SUMIFS({Helper Column}, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

Aaron Kennedy

To Paul: It works! thank you!

Paul Newcome

Happy to help. 👍️