GENERATING WEEKLY REPORT FORMULA

Mariniel Bautista

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.

Paul Newcome

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.

Paul Newcome

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.

Mariniel Bautista

Thanks. I figured it out as well from other community discussion.

Paul Newcome

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.