Countifs without including duplicates

DBMiller

Hello,

Im trying to get information to show on a dashboard that breaks down events by type and date range. I was able to get the following formula to work on a roll up sheet:

=COUNTIFS({[sheet] Range 2}, <=DATE(2021, 1, 31), {[sheet] Range 2}, >=DATE(2021, 1, 1), {[sheet] Range 1}, "task type")

the problem is that multiple rows in my sheet are tied to the same work order number so I end up with duplicates, which inflates the numbers for the month.

Im trying to figure out how to add the "work order number" column to this formula so that it only counts each work order once if it falls within the month and meets the "task type" criteria.

Paul Newcome

Try something like this...

=COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, IFERROR(YEAR(@cell), 0) = 2021, {Type Column}, @cell = "task type")))

Paul Newcome

Try something like this...

=COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, IFERROR(YEAR(@cell), 0) = 2021, {Type Column}, @cell = "task type")))

DBMiller

Paul, Thank you! I replaced the IFERROR section of the formula and replaced it with the date range from my sample formula so I pulled just the orders from the month.

Paul Newcome

Happy to help. 👍️

I used the IFERROR/YEAR because I misread your original dates and thought you were pulling for all of 2021. My apologies.

DBMiller

no worries, the IFERROR/YEAR is still helpful to me because im unfamiliar with how to use that formula so now I'll be able to use it going forward.

Paul Newcome

You can also combine that with IFERROR/MONTH to specify a year and month instead of having to manually enter the first and last date.

In the below formula you will see an example of that as well as an example of how to combine criteria for the same range without having to repeat the range multiple times. It comes in handy if you have a lot of different ranges with multiple criteria each or if you have quite a few different criteria sets for the same range.

=COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Type Column}, @cell = "task type")))

We put all of our {Date Column} criteria together without having to repeat the range, and we specified a month and year instead of a start and end date.

DBMiller

wow, super helpful. thanks again!

Paul Newcome

Sure thing! 🙂