0

Column:Row G1, H1 create the data from Sheet1 and J2 formats it for cell A1 in Weekday order.

Please note also cell A12 which creates a Monthly summary directly from Sheet1.

Can this sheet (Day / Month) be simplified and get rid of G,H,J and K ?

https://docs.google.com/spreadsheets/d/1pOm6tWUHI9xoJuXuu64vQT7QryqpBQxYBMce7NAcgEY/edit?usp=sharing

1 Answer 1

1

I've added a new sheet ("Erik Help") to your sample spreadsheet. It contains two formulas.

In A1:

=ArrayFormula(QUERY(FILTER({WEEKDAY(Sheet1!A2:A),Sheet1!E2:E},ISNUMBER(Sheet1!E2:E),Sheet1!E2:E>0),"SELECT Col1, COUNT(Col1), SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Day', COUNT(Col1) 'Count by Day', SUM(Col2) 'Sum by Day'"))

In A12:

=ArrayFormula(QUERY(FILTER({VALUE(TEXT(Sheet1!A2:A,"mmm yyyy")),Sheet1!E2:E},ISNUMBER(Sheet1!E2:E),Sheet1!E2:E>0),"SELECT Col1, COUNT(Col1), SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL COUNT(Col1) '', SUM(Col2) ''"))

Each references only Sheet1.

The main thing to notice in each of these formulas is that I performed a FILTER before applying the QUERY. While the FILTER makes it necessary to LABEL columns at the end of the QUERY, it is actually still much more efficient, as you can see.

Also note that I applied a CF rule to the range A12:C, to highlight every other non-blank row.

3
  • Perfect! Not sure why but had to add format Col1 'dddd' to the A1 query and format Col1 'mm mmm' to A12 - as otherwise it just showed weekday numbers and 5 digit numeric value for month - but absolutely works fine and is simpler - just what I wanted. Thank you.
    – rosa
    Feb 6, 2021 at 20:28
  • One followup (if allowed?). If the Sheet1 did not have already a total in Col E, would it be possible to Count the number of days (mm mmm) in Cell A12 for the Day / Month sheet ?
    – rosa
    Feb 6, 2021 at 21:15
  • Rather than adding more to the QUERY to format things, I just formatted the ranges directly using Format > Number > More formats. As for your second question, the goal of this site and others like it is not only to help individuals but to make it easier for future site visitors to find answers to their similar questions. For that reason, it is best to start a new post with your second question. But the short answer is, yes, it is possible to eliminate Column E in Sheet1 and still get the same results in the second sheet.
    – Erik Tyler
    Feb 7, 2021 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.