Summarizing Data from Row to Column Uniquely by Date and Product Name

user_12732980 0
Viewed 38

In google sheet, is there any way to summarize data that is stored in row to be listed in column?

  1. I have a list of transaction data.
  2. the product data is stored in row from left to right
  3. I need to summarize the total transaction for each date and each product to my format, that is stored in column from top to bottom
  4. the summary must be unique for date and product
  5. Array Formula is required since it pains me if I had to drag again and again once the data become bigger and bigger

Here is the dummy link: https://docs.google.com/spreadsheets/d/1w1_p9LdBzFuwozLZwrp04pCs-dyzsZ7e2VlWH1b7_9w/edit?usp=sharing

"transaction" sheet contains the transaction data while "summary" sheet contains the output that I need

Thankyou!!

summarization array-formulas spreadsheet transpose google-sheets
1 Answers

try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(
 IF(transaction!C2:E="",,transaction!B2:B&"×"&transaction!C2:E)), "×"), 
 "select Col1,Col2,count(Col2) 
  where Col2 is not null
  group by Col1,Col2
  label Col1'date',Col2'product',count(Col2)'qty'"))

enter image description here

user_56326290
Related