Unique Count with Multiple Criteria

AWard

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?

AravindGP

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.

AWard

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))

Paul Newcome

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")