Specialized Report

I have a report that goes out weekly that looks like this:

As you can see it shows in one small area, reservations, contracts, cans, closings and traffic for the week, month, quarter, year and for the entire project.

I'd like to find a way to do this same report in SS. Here is my source sheet that is input via a form and it collects a lot of other info that goes into other detailed reports, but I'd like to recreate the above report or something similar. Is there a way to do the above in a single report?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    To sum for current quarter we first need to establish what the current quarter is. To do this we would use a nested IF.

    =IF(TODAY()>= DATE(YEAR(TODAY()), 10, 1), 4, IF(TODAY()>= DATE(YEAR(TODAY()), 7, 1), 3, IF(TODAY()>= DATE(YEAR(TODAY()), 4, 1), 2, 1)))


    Now that we have that, we can leverage it to say SUMIFS the number column based on the quarter helper column being equal to the above nested IF and the year being the current year.

    =SUMIFS({Number Column}, {Quarter Helper Column}, @cell = nested_if, {Date Column}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))


    =SUMIFS({Number Column}, {Quarter Helper Column}, @cell = IF(TODAY()>= DATE(YEAR(TODAY()), 10, 1), 4, IF(TODAY()>= DATE(YEAR(TODAY()), 7, 1), 3, IF(TODAY()>= DATE(YEAR(TODAY()), 4, 1), 2, 1))), {Date Column}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Answers