COUNTIFS for multiple columns to include date parameters

Sydney Eley

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!

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.

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