COUNTIFS for multiple columns to include date parameters

Hi,

I am new to smartsheet and still learning. I am working on a dashboard and trying to create a metric sheet so that I can plug in data to my dashboard. I am trying to calculate:

How many events (wedding, bachelor party, bachelorette party) occurred in individual states (TN, MS, SC) month on end?

I have tried multiple formulas and continue getting unparseable. This was my last attempt:

=countifs({location}, (Contains(“TN”)),{Category of Event}, (Contains(“wedding”)), {Date of Event}, @cell<Date(2023,7))


Any help would be greatly appreciated!

Thank you!

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭

    @Sydney Eley

    Can you drop a shot of your source sheet, and review how the columns are configured, some formulas work better with drop downs vs free text?

    I should be able to help with the formula if you want to tag me in your upload.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The first thing I notice is that you are using more parenthesis than you need. While technically you have used them correctly, everything you can cut out is one less thing to accidentally mess up.

    =countifs({location}, Contains(“TN”),{Category of Event}, Contains(“wedding”), {Date of Event}, @cell<Date(2023,7))


    Next we have your CONTAINS functions. They need a range of "@cell".

    =countifs({location}, Contains(“TN”, @cell),{Category of Event}, Contains(“wedding”, @cell), {Date of Event}, @cell<Date(2023,7))


    Next your date criteria. If you are looking for a specific date, you need to include the day portion of the function for it to work. If you are looking for a specific month within a specific year, you would call those out individually.

    =countifs({location}, Contains(“TN”, @cell),{Category of Event}, Contains(“wedding”, @cell), {Date of Event}, @cell<Date(2023,7, 31))

    =countifs({location}, Contains(“TN”, @cell),{Category of Event}, Contains(“wedding”, @cell), {Date of Event}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2023))


    And last but certainly not least... Your quotes. See how they are slanted? Those are called "smart quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet formula. You are going to need to retype them here in the community (as below), directly in Smartsheet, or in a text editor program such as Notepad (not Word).

    =countifs({location}, Contains("TN", @cell),{Category of Event}, Contains("wedding", @cell), {Date of Event}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2023))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!