COUNTIF Year to Date

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!

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.