2

I am aware that I may be asking the wrong question here. Not sure how to ask, but I am trying to create a budget on google sheets that to record how much I spend on each day. Right now I have the transaction shown below:

Date | Category | Amount  
--------------------------
Jan 1| Dining   | $5 
Jan 1| Dining   | $30
Jan 1| Gas      | $20
Jan 2| Other    | $15

I want to know how I could use the information I have above to create the table shown below:

Date | Dining| Gas| Other
------------------------
Jan 1| $35   | $20| $0
Jan 2| $0    | $0 | $15

I'm stuck trying to use a LOOKUP and SUMIF together.

2

The manual solution would be to create a Pivot table of your data.

However, if you are looking for a formula solution, you can create a pivot table by using a query formula:

={query(A1:C,"Select A, Sum(C) where A is not null group by A Pivot B limit 0",1);
ArrayFormula((N(Query(query(A1:C,"Select A, Sum(C) where A is not null group by A Pivot B",1),
"Select * offset 1",0))))}

enter image description here

Or you could just use the simplest version of it:

=query(A1:C,"Select A, Sum(C) where A is not null group by A Pivot B",1)

but you will get empty cells when the amounts are zero.

5
  • 1
    Correct Marios. I too would go for the formula. Still. I think you over complicated it. One could use a much simpler version like: =query(A1:C,"Select A, Sum(C) where A is not null group by A Pivot B",1) – marikamitsos Jan 10 at 18:07
  • @marikamitsos the OP wants to put zeros when the amount is 0$ so I just wanted to stick with that. But besides that then yes the single formula is much easier. Thanks a lot for your feedback :) – Mario Jan 10 at 19:33
  • Wow, this is perfect, and complicated. Thank you. Time to learn more about pivots, queries and arrayformula – Beginner Programmer Jan 11 at 0:11
  • @Marios Do you know how I could include dates that doesn't contain any value? For example if we change Jan 2 to Jan 3. Is it possible to have the Jan 2 row still existing with $0's? – Beginner Programmer Jan 12 at 18:28
  • I am not sure if that's possible. Could you post a new question so other readers would be able to help you out ? @BeginnerProgrammer – Mario Jan 12 at 18:31

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.