How to find Max Daily Sold and Average Daily Sold in Google Sheets?

user_7771809 0
Viewed 64

I have a spreadsheet that records daily sales. Sample data is as follows. Please help me with the formula in E4 to E5 (maximum daily count of a particular SKU) and F4 to F5 (average daily count of a particular SKU).

enter image description here

google-sheets-formula google-sheets
1 Answers

I suggest nested queries like this:

=query(
QUERY(A:B,"SELECT B,COUNT(A) where A is not null GROUP BY A,B"),
"select Col1,max(Col2),avg(Col2) group by Col1 label max(Col2) 'Max of daily sold',avg(Col2) 'Average of daily sold'")

enter image description here

Note: this agrees with OP's results, but average is only calculated over days where there has been a sale of a particular item, so the correct result for Item_A should (I think) be 1.5.

EDIT

If the SKU's were prefilled in (say) D1:D3, you could use a lookup to get the values from the nested queries:

=ArrayFormula(
vlookup(D1:D3,query(
QUERY(A:B,"SELECT B,COUNT(A) where A is not null GROUP BY A,B"),
"select Col1,max(Col2),avg(Col2) group by Col1 label max(Col2) 'Max of daily sold',avg(Col2) 'Average of daily sold'"),{2,3},false))
user_38949170
Related