0

Here is an example Google Sheet that summarizes Col A, D by weekday totals and counts.

I would like to add an Average number of items by Weekday, like K9 L9 but show it in formula K1.

There are two(2) Mondays and one had 10 rows, the other 2 so the average is 6 for Mondays so far.

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

This is the formula in K1:

=QUERY(Query(A1:D, "select (A), sum(D) 
                    where A is not null and dayOfWeek(A)<>1
                    group by (A) 
                    order by  dayOfWeek(A) "),
              "select dayofWeek(Col1), Count(Col1), Sum(Col2)
               group by dayofWeek(Col1) 
               label dayOfWeek(Col1) 'Weekday', sum(Col2) 'Total', count(Col1) '#' 
               format dayOfWeek(Col1) 'dddd', sum(Col2) '###0.' ")
1

Try this formula

=QUERY({ QUERY( Query(A1:D, "select (A), sum(D) where A is not null and dayOfWeek(A)<>1 group by (A) order by dayOfWeek(A) "), "select dayofWeek(Col1), Count(Col1), Sum(Col2) group by dayofWeek(Col1) label dayOfWeek(Col1) 'Weekday', sum(Col2) 'Total', Count(Col1) '#' format dayOfWeek(Col1) 'dddd', sum(Col2) '###0.' "), QUERY({QUERY({A1:D},"select dayofweek(Col1)")},"select Col1, count(Col1) where Col1 is not null group by Col1 ")},"select Col1, Col2, Col3, (Col5/Col2) label (Col5/Col2) 'Avg' ")

This formula is the same as yours with the addition of an extra query that:

  • calculates a total count of dayofweek -> count(Col1)
  • divides the count by the count in the # field.

Output

enter image description here

1
  • Fantastic. Thank you
    – rosa
    Mar 26 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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