Google-sheets – How to filter based on multiple cases

google sheets

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.

Best Answer

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.

Related Topic