2

I am trying to create a document which automatically tracks spending based on two categories.

For example, I have a sheet formatted similar to the table below:

Type    |   Amount  |   Category  
Visa    |   $100    |   Hair  
Chase   |   $200    |   Face  
Visa    |   $50     |   Hair  
Master  |   $150    |   Face  
Visa    |   $25     |   Face  

And my goal is to update a seperate table with spending broken down for both "Type" and "Category", like this:

        |   Total  |   Hair  |   Face   |   Mouth
Total   |   $525   |   $150  |   $375   |   $0   
Visa    |   $175   |   $150  |   $25    |   $0   
Chase   |   $200   |   $0    |   $200   |   $0   
Transfr |   $0     |   $0    |   $0     |   $0   
Master  |   $150   |   $0    |   $150   |   $0  

Note: all categories in the above table are intended to be static with only their relevant values getting filled in. That's why "Mouth" and "Transfr" are part of the table, even though they have no transactions in that category.

I have attempted to use the "Query" formula but I am only able to sort based on one category receiving results like these:

        |   Sum
Visa    |   $175  
Chase   |   $200 
Transfr |   $0   
Master  |   $150 

or

        |   Sum
Hair    |   $300  
Face    |   $225 

I am not sure if this is possible on Google Sheets, but any help would be appreciated.

  • To sort the QUERY result, use the order by clause. – Rubén Jan 4 at 3:48
1

i

  • C11: =SUM(C3:C9)
  • C12: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where B='"&$B12&"'")),0)
  • D11: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where D='"&D$10&"'")),0)
  • D12: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where B='"&$B12&"' and D='"&D$10&"'")),0)
  • F15: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where B='"&$B15&"' and D='"&F$10&"'")),0)
  • etc.

demo spreadsheet

  • Yes! This worked, thank you very much – Joshua K Jan 4 at 6:28

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.