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
Best Answer
-
@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
-
@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)
-
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!
-
That's what the community is for! π
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!