Unique Count with Multiple Criteria
Hello.
I am looking to count the total number of encounters by month within each specified postal code and am unsure of the formula to use. I have tried countifs, distinct, collect etc.. and am not getting the results I had hoped for. I have attached a picture of the data I am hoping to summarize
What formula do you recommend for this?
Answers
-
Hi @AWard
If you're trying to get the number of times a postal code appeared in a month, you can achieve it using the following two options.
Option 1: Create a helper column to get the month and year of the record. Your formula will be =MONTH(Date@row) + " " + YEAR(Date@row). This will give you a result of 3 2024 for any record which has a date in March of 2024.
You can then do =COUNTIFS([Helper column]:[Helper column], "3 2024", [Postal Code]:[Postal Code], "N4T1T8") to get the count of times the postal code N4T1T8 has appeared in the month of March 2024. You can replace the month criteria to other months and the postal code criteria to other postal codes for the occurrences.
Option 2: Add the date range in the formula directly. =COUNTIFS(Date:Date, >=DATE(2024,03,01),Date:Date, <=DATE(2024,03,31), [Postal Code]:[Postal Code], "N4T1T8"). In this replace the date range to the dates of other months and the portal codes for the occurrences.
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Sorry I should have been more clear it what I was asking. I am not looking for a specific postal code, but rather the first 3 digits, would I then insert a contains formula in front of the postal code? ex.=COUNTIFS(Date:Date, >=DATE(2024,03,01),Date:Date, <=DATE(2024,03,31), [Postal Code]:[Postal Code], CONTAINS( "N4T"@cell))
-
It would be more like this:
=COUNTIFS(Date:Date, >=DATE(2024,03,01),Date:Date, <=DATE(2024,03,31), [Postal Code]:[Postal Code], LEFT(@cell, 3) = "N4T")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!