CountIfs with Distinct and a date range
I am sure it is doable, but I continue to struggle to get CountIFS, AND, and DISTINCT to work together. I need to count distinct dates in a long column and need to determine how many distinct dates there were in particular months.
I'm able to use this to see distinct counts after a certain date:
=COUNTIFS(DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), >DATE(2023, 6, 30))
However, I need to also limit the returning value to an end date, in that particular case, <DATE(2023, 8, 1).
How can I get a "between" value to work, here?
If there's a better way, maybe something with the MONTH function, please let me know!
Best Answer
-
Hi @A.J.
You can use this formula
=COUNTIFS(DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), >DATE(2023, 6, 30), DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), <DATE(2023,8,1))
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers
-
Hi @A.J.
You can use this formula
=COUNTIFS(DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), >DATE(2023, 6, 30), DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), <DATE(2023,8,1))
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
You actually need a COUNT/DISTINCT/COLLECT combo.
=COUNT(DISTINCT(COLLECT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date], [CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date], AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2023))))
-
hi @Paul Newcome. I used the formula that was provided at the top and it worked. Can you tell me what the outcome difference would be for the one you suggested?
Thank you, as always, for your terrific help!
-
Shouldn't be a difference in outcome. It is more about efficiency, readability, and management.
Mine says we are going to COLLECT everything from range 1 where range 2 (in this case the same as range 1) meets the criteria. Then COUNT the DISTINCT entries.
I also prefer to run month/year calculations without using specific dates as (for me at least) it is easier to just plug in the month and year number that I am looking for.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!