Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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
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.
