GENERATING WEEKLY REPORT FORMULA
I would like to know what formula should I use if I want to produce a weekly report from this database? So the client wants us to give weekly report containing total number of request received for the week, also how many were completed, still pending or on going. (This is just the sample data as reference). Also, what formula if I want to produce a weekly report containing how many request was received from each bank branch, and how many were received per type of work?
Thanks in advance for your answers.
Best Answer
-
You would want to create COUNTIFS formulas. The basic structure is
=COUNTIFS(criteria range 1, criteria 1, criteria range 2, criteria 2, ..........................................)
So for example, to count how many tickets have been resolved:
=COUNTIFS([Date Resolved]:[Date Resolved], ISDATE(@cell))
Tickets resolved for Makati City branch:
=COUNTIFS([Date Resolved]:[Date Resolved], ISDATE(@cell), [Bank Branch/Property Name]:[Bank Branch/Property Name], "Makati City")
Tickets resolved for Makati City branch during January 2020
=COUNTIFS([Date Resolved]:[Date Resolved], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020), [Bank Branch/Property Name]:[Bank Branch/Property Name], "Makati City")
The exact formulas would depend on your exact requirements, but that should at least get you started down the right path.
Answers
-
You would want to create COUNTIFS formulas. The basic structure is
=COUNTIFS(criteria range 1, criteria 1, criteria range 2, criteria 2, ..........................................)
So for example, to count how many tickets have been resolved:
=COUNTIFS([Date Resolved]:[Date Resolved], ISDATE(@cell))
Tickets resolved for Makati City branch:
=COUNTIFS([Date Resolved]:[Date Resolved], ISDATE(@cell), [Bank Branch/Property Name]:[Bank Branch/Property Name], "Makati City")
Tickets resolved for Makati City branch during January 2020
=COUNTIFS([Date Resolved]:[Date Resolved], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020), [Bank Branch/Property Name]:[Bank Branch/Property Name], "Makati City")
The exact formulas would depend on your exact requirements, but that should at least get you started down the right path.
-
Thanks. I figured it out as well from other community discussion.
-
Happy to help! 👍️
Please don't forget to flag the most appropriate response(s) as "helpful" so that others looking for a similar solution know that one can be found here.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!