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

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).

CodePudding user response：

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'")

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.

CodePudding user response：

You can obtain the same result without any formula, use a pivot table.