COUNTIF Year to Date

Madison H

I have connected our website contact form submissions into SmartSheet. I am creating a dashboard including how many form submissions we've gotten in a certain date range... for example, year to date, quarter to date, month to date, etc.

How would I go about pulling this information? I am using a separate Smartsheet to do the calculations for it so I don't mess up any data, so I have to reference my original sheet to pull the info.

I have a column that has a "created date" category which includes the time they submitted as well, so I'm not sure if that is creating an issue? I also created another column which just pulls the date. I have tried the formula below but it says the count is 0 (from manually counting, it should be 53). The {Website Form Submission Range 2} appears after I click on the "Date" column.

=COUNTIF({Website Form Submission Range 2}, ">=DATE(2024, 01, 01)")

I also categorize the different submissions, so I will be needing to calculate how many form submissions we've gotten in each category. Would this be a COUNTIFS formula? Still staying within the year to date, quarter to date, month to date, etc. categories.

Categories would be "Recruiting" or "Service", for example.

Thank you!

brianschmidt

I was having trouble getting a formula to work without the addition of helper columns. So, this may be less efficient of a way to do it, but it looks like it will work. I added a helper column to pull out the month of a submission date and a separate column to pull out the year of a submission date with the following column formulas respectively:

=IFERROR(MONTH(DATE@row), "")

=IFERROR(YEAR(DATE@row), "")

Then, instead of a separate sheet, I just created the count formulas in the summary data of the same sheet. The assumption would then be that you pull a Summary Report and use that for your dashboard (or you could just use the summary data in a metric widget on the dashboard). However, you could swap out references in the following formula to cross-sheet references if you wanted it in a separate sheet.

For month to date (from the current calendar year), I had the following formula:

=COUNTIFS(Month:Month, @cell = MONTH(TODAY()), Year:Year, @cell = YEAR(TODAY()), DATE:DATE, NOT(ISBLANK(@cell)))

Then, for current year to date counts, I had the following formula:

=COUNTIFS(Year:Year, @cell = YEAR(TODAY()), DATE:DATE, NOT(ISBLANK(@cell)))

In order to add category conditions, your formula would just be edited with those criterion (see examples below):

=COUNTIFS(Year:Year, @cell = YEAR(TODAY()), DATE:DATE, NOT(ISBLANK(@cell)), Category:Category, "Service")

=COUNTIFS(Year:Year, @cell = YEAR(TODAY()), DATE:DATE, NOT(ISBLANK(@cell)), Category:Category, OR("Service", "Recruiting"))

Hope this helps!:)

Scott Orsey

Hi @Madison H and @brianschmidt , I like Brian's solution. If you create those helper columns (and I might consider adding a Quarter helper also) you can use a Row Report and Grouping/Summarize to get all sorts of information.... current, YTD, Quarterly and history.