CODEWITHSUNDEEP
google-sheets
Linux Noob
Linux Noob

Reputation: 35

Google sheets sumifs with multiple conditions not working as expected for splitting and summing across months

I have a sheet where I am managing personal finance, and for calculations I am using few formulae, everything is working except for one formula which isn't working.

I want to split the expenses month-wise for a specific category, when I use condition to sum all the expenses for that category across all the months, it works fine giving me the total

Here's the sample page:

https://docs.google.com/spreadsheets/d/1pQu9tKuCP2GU7apEtX3Gp6xCb7K1THl0dDCXEiqu0bU/edit?usp=sharing

Example:

=SUMIFS('Other Expenses'!E2:E,'Other Expenses'!C2:C,"Eat out",'Other Expenses'!G2:G,TRUE)

The above formula gives me how much I spent for "Eat out" category across all the months. But when I try to split among months, it doesn't happen.

I have tried a few things like the below, with no luck unfortunately.

=ARRAYFORMULA(SUMIF('Other Expenses'!C2:C&'Other Expenses'!A2:A,"Eat out"&'IF(TEXT('Other Expenses'!A2:A,"MMMM-YYYY")=C2)','Other Expenses'!E2:E))

=QUERY('Other Expenses'!A1:G,"Select A,C,E where A > date '"&TEXT(A1,"yyyy-mm-dd")&"' and C = 'Eat out'",1)

Query gives me the filtered list of all the expenses for a category, "Eat out" in this case, but I am unable to process it further.

Any help would be appreciated. Thank you

FYI: I am not an expert on sheet or excel, this is just something which I do for personal tracking.

Upvotes: 1

Views: 138

Answers (2)

Nabnub
Nabnub

Reputation: 1055

I'm not sure if you wanted to filter your data by Category or by Description, in any case you can easily change the columns as needed and try the following:

enter image description here

See your spreadsheet tab called Nabnub: HERE

First Formula in A7:

= {query({ArrayFormula(PROPER(text('Other Expenses'!A2:A, "MM-MMMM"))),'Other Expenses'!B2:B,'Other Expenses'!E2:E}, 
   "Select Col1 ,sum(Col3) where Col1 is not null and Col2='"&A6&"'
        group by Col1 Order By Col1 asc label sum(Col3)
           'Total', Col1 'Months'"); 
   {"TOTAL 2021",SUMIF('Other Expenses'!B:B,A6,'Other Expenses'!E:E)}}

Second Formula in A22:

= {query({ArrayFormula(PROPER(text('Other Expenses'!A2:A, "MM-MMMM"))),'Other Expenses'!C2:C,'Other Expenses'!E2:E}, 
   "Select Col1 ,sum(Col3) where Col1 is not null and Col2='"&A22&"'
        group by Col1 Order By Col1 asc label sum(Col3)
           'Total', Col1 'Months'"); 
   {"TOTAL 2021",SUMIF('Other Expenses'!C:C,A22,'Other Expenses'!E:E)}}

Third formula in D7:

= {query({'Other Expenses'!B2:B,'Other Expenses'!E2:E}, 
   "Select Col1 ,sum(Col2) where Col1 is not null 
        group by Col1 Order By Col1 asc label sum(Col2)
           'Total', Col1 'By Description'"); 
   {"TOTAL 2021",SUM('Other Expenses'!E2:E)}}

Fourth formula in G7:

= {query({'Other Expenses'!C2:C,'Other Expenses'!E2:E}, 
   "Select Col1 ,sum(Col2) where Col1 is not null 
        group by Col1 Order By Col1 asc label sum(Col2)
           'Total', Col1 'By Category'"); 
   {"TOTAL 2021",SUM('Other Expenses'!E2:E)}}

Fifth formula in J7:

= {query({ArrayFormula(PROPER(text('Other Expenses'!A2:A, "MM-MMMM"))),'Other Expenses'!E2:E}, 
   "Select Col1 ,sum(Col2) where Col1 is not null 
        group by Col1 Order By Col1 asc label sum(Col2)
           'Total', Col1 'Months'"); 
   {"TOTAL 2021",SUM('Other Expenses'!E2:E)}}

Sixth formula in M7:

= {query({ArrayFormula(PROPER(text('Other Expenses'!A2:A, "MMMM"))),'Other Expenses'!B2:B,'Other Expenses'!E2:E}, 
   "Select Col2 ,sum(Col3) where Col2 is not null and Col1='"&M6&"'
        group by Col2 Order By Col2 asc label sum(Col3)
           'Total', Col2 'Months'"); 
   {"TOTAL",SUMIF(ArrayFormula(PROPER(text('Other Expenses'!A:A, "MMMM"))),M6,'Other Expenses'!E:E)}}

Upvotes: 1

Mike Steelson
Mike Steelson

Reputation: 15328

I guess that the simpliest solution is to perform a pivot table. And then group by monthes and categories.

Upvotes: 0

Related Questions