Breakdown of creation date by year and month

Hi,

I'm very new to formulas and I've not set up smartsheets previously

We currently use a form for external parties to complete, that I then am trying to build a dashboard to have an overview of.

I need to show how many forms were completed in both a year in total and also a monthly breakdown.

My by year formula is this: =COUNTIFS({Date Created}, >=DATE(2022, 1, 1), {Date Created}, <=DATE(2022, 12, 31))

But I don't know how to do by month as this one: =COUNTIFS({Date Created}, IFERROR(MONTH(@cell), 0) = 1) shows all january submissions rather than January in a specific year

Hope that make sense

Thanks

Kay

Find more posts tagged with

Formulas

Accepted answers

Samuel Mueller

@KayH if you want to add criteria for year as well, add that to your countifs formula

=COUNTIFS({Date Created}, IFERROR(MONTH(@cell), 0) = 1, {Date Created}, IFERROR(YEAR(@cell), 0) = 2022)

also instead of using this formula: =COUNTIFS({Date Created}, >=DATE(2022, 1, 1), {Date Created}, <=DATE(2022, 12, 31))

You could just use : =COUNTIFS({Date Created}, IFERROR(YEAR(@cell), 0) = 2022)

All comments

Samuel Mueller

@KayH if you want to add criteria for year as well, add that to your countifs formula

=COUNTIFS({Date Created}, IFERROR(MONTH(@cell), 0) = 1, {Date Created}, IFERROR(YEAR(@cell), 0) = 2022)

also instead of using this formula: =COUNTIFS({Date Created}, >=DATE(2022, 1, 1), {Date Created}, <=DATE(2022, 12, 31))

You could just use : =COUNTIFS({Date Created}, IFERROR(YEAR(@cell), 0) = 2022)

KayH

Thank you SO much Samuel, I really appreciate your help... I dare say I may need it a few more times by the end of this project!

Samuel Mueller

That's what the community is for! 😀

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.