Count number of events this WTD, MTD, QTD and YTD
Hi,
I'm looking for a formula that I could use to count number of events that were reported in the current week, month quarter and year to date. The reported date is autopopulated in a standard date format and the count will be in the Sheet Summary. thank you for all your suggestions
Best Answer
-
This could be easier with helper columns and/or 'Helper Sheet Summary Fields' but can be done as shown below
WTD:
=COUNTIFS(Date:Date, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
MTD:
=COUNTIFS(Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
QTD:
=COUNTIFS(Date:Date, AND(IFERROR(ROUNDUP(MONTH(@cell) / 3, 0), 0) = ROUNDUP(MONTH(TODAY()) / 3, 0), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
YTD:
=COUNTIFS(Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
Answers
-
Can you share some screen shots of your columns and it's content? When you say reported date is autopopulated, is it the created by date type or is it a date column that has an automation to capture the current date? Are the different events columns? Please provide more details since there are many ways to set up a sheet to do something like this.
-
This could be easier with helper columns and/or 'Helper Sheet Summary Fields' but can be done as shown below
WTD:
=COUNTIFS(Date:Date, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))
MTD:
=COUNTIFS(Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
QTD:
=COUNTIFS(Date:Date, AND(IFERROR(ROUNDUP(MONTH(@cell) / 3, 0), 0) = ROUNDUP(MONTH(TODAY()) / 3, 0), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
YTD:
=COUNTIFS(Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
thank you so much Eric and Leibel. I used your formulas Leibel and it works fine for me. Thank you!
-
Sooooooo helpful! Thanks so much!
Continuous Improvement Facilitator in HVAC industry || Timezone CES
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
