Breakdown of creation date by year and month

Options
KayH
KayH
edited 01/03/23 in Formulas and Functions

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

Tags:

Best Answer

  • Samuel Mueller
    Samuel Mueller Overachievers
    Answer ✓
    Options

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

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    Answer ✓
    Options

    @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
    Options

    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
    Samuel Mueller Overachievers
    Options

    That's what the community is for! 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!