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

  • AravindGP
    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.

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!