How to count DISTINCT countries within a date range?
Quarterly, I am required to show how many DISTINCT countries were reached by our instructors. I need a formula that would count the DISTINCT countries reached between (for example) 1/1/2020 thru 3/31/2020 (for first Quarter reporting). I've attached a snippet from my Smartsheet to show the setup.
I have the formula for counting DISTINCT countries, but I need to add the date range.
=COUNT(DISTINCT[LOCATION COUNTRY]1:[LOCATION COUNTRY]114))
Thank you for your help!!!
Kitty Sandel
Answers
-
Try using a COLLECT function in the mix...
=COUNT(DISTINCT(COLLECT([LOCATION COUNTRY]1:[LOCATION COUNTRY]114, [Teaching End Date]1:[Teaching End Date]114, @cell >= DATE(2020, 1, 1), [Teaching Start Date]1:[Teaching Start Date]114, @cell <= DATE(2020, 3, 31))))
Help Article Resources
Categories
Check out the Formula Handbook template!