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")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try something like this...
=COUNT(DISTINCT(COLLECT({Work Order # Column}, {Date Column}, IFERROR(YEAR(@cell), 0) = 2021, {Type Column}, @cell = "task type")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
wow, super helpful. thanks again!
-
Sure thing! 🙂
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!