# How to filter based on multiple cases

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.

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))))}
``````

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.

• 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