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
-
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
-
You would need to set up a separate sheet and use formulas with cross sheet references to get your counts. Set up a report to look at this metrics sheet, then set the report to send on the recurring basis.
-
@Paul Newcome, that makes perfect sense.
I already have helper columns for the week, month, quarter, and year. Do you know the best way to write a formula to total everything for the previous week by week number? I don't want to do Today -7 as that is a rolling total, I want to automatically show last week without having to update the week number. Meaning, I want to total all the reservations made last week using the Reservation column and the Week column. Any ideas?
-
@Paul Newcome, I've got week, month and year figured out on the lookup sheet.
I'm trying to find a way to sum if it's this quarter and this year. I have a quarter helper column. I'm thinking a sumifs on that and the year associated with Today. Thoughts?
-
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()))
-
@Paul Newcome, this is amazing!! However, I'm getting 0 when it should be 4 so I must have something wrong. I double-checked all the references and I ran a filer on the original sheet to see that these columns have the correct information. How can I troubleshoot this further?
=SUMIFS({Reservations}, {Quarter}, @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}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))
-
How exactly is your quarter helper column populated?
-
@Paul Newcome, that took care of it. My quarter formula had quotes around the number for the quarter. Once I removed that and made it a number, your formula works perfectly.
Thank you SO much for your help and great explanation of the process to get to what I needed. It truly helps! With your guidance and help, I'll be able to create my dashboard reports!
Have a great week!
-
That's exactly what I was thinking when I asked. Glad you got it sorted and always happy to help. 👍️
-
Hey @Paul Newcome,
Continuing with the sum by week, month, qtr, etc., I've created another summary sheet to track sources by those timelines.
I thought the formulas you gave me would work with a MATCH or CONTAINS function, but I don't have this setup correct because I can get components of the formula to work separately, but not together.
I've tried so many things I've lost track of, so I'd love your help to set up the Week to date to sum of {Traffic 1} where the {Date} in the source sheet is this week and the {Traffic Source} in the source sheet matched the [Summary] column at that row.
Where you see the error, I have the following formula:
=SUMIFS({Traffic 1}, {Date}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), CONTAINS(Summary@row, @cell, {Traffic Source}))
Would MATCH work better or is it simply the way format I'm using?
I appreciate your help!!
-
It is because of the last bit of your formula. It should be "range, criteria".
You have
=SUMIFS(................................, CONTAINS(Summary@row, @cell, {Traffic Source}))
The range should be before the criteria and not inside of the CONTAINS function. The "@cell"
acts as the range portion for the CONTAINS function. Try this adjustment...
=SUMIFS({Traffic 1}, {Date}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {Traffic Source}, CONTAINS(Summary@row, @cell))
-
@Paul Newcome the formula works perfectly, thank you!! Thanks again for the explanation, it truly helps me.
-
Happy to help. 👍️
