CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
nachocab
nachocab

Reputation: 14404

How to sum by category in Google Sheets and manipulate the result?

I have this format:

A       B    C       D  
invoice date product amount
         D1   P1      10 
         D1   P2      100
         D1   P1      10
I1       D1   P1      10

I want to calculate the sum of non-invoiced products by product. I'm almost there with this:

=query($A$2:$D;"select C, sum(D) where A is null group by C")

   sum 
    
P1 20
P2 100

But I actually want to split this result (ignoring the "sum" header) and put it into two non-adjacent columns E and G:

       E   F   G  
row1  P1      20
ro22  P2      100

I guess I could write two separate array queries, one for E1 and another for G1, but I'm still not sure how to extract pieces of the QUERY result (and there might be a simpler approach than using QUERY).

Live example

Upvotes: 0

Views: 386

Answers (1)

Nikko J.
Nikko J.

Reputation: 5543

Unfortunately, ignoring columns in a formula is not possible. You need to create 2 QUERY functions.

Try these formulas:

For cell E8: =QUERY(QUERY(A2:D5, "SELECT C, SUM(D) where A is null group by C label SUM(D) ''"), "SELECT Col1")

For cell G8: =QUERY(QUERY(A2:D5, "SELECT C, SUM(D) where A is null group by C label SUM(D) ''"), "SELECT Col2")

Example:

enter image description here

enter image description here

Reference:

Upvotes: 1

Related Questions