Countifs without including duplicates
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.
Best Answer
-
Try something like this...
=COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, IFERROR(YEAR(@cell), 0) = 2021, {Type Column}, @cell = "task type")))
Answers
-
Try something like this...
=COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, IFERROR(YEAR(@cell), 0) = 2021, {Type Column}, @cell = "task type")))
-
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.
-
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.
-
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.
-
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.
-
wow, super helpful. thanks again!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!