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.