# Looking for something simpler than SUMIFS - SUMIFS

1. ## Looking for something simpler than SUMIFS - SUMIFS

Hey everyone, I'm new here.

I'm building a very complicated expense tracking sheet, where the sheet of transactions has the following fields:
Date, Type (Income/Expense), Form (Cash/Bank), Category (salary, food, expense reimbursement, credit card, etc...), and amount.

Another sheet, I have built a table to summarize it for me as follows:

Columns are Month/Year, and rows are Income, Expense, and Net

For income, I want the formula to first pick items which are Income, and then I need it to include all types of income except things like expense reimbursements and transactions that are just moving cash from one pocket to the other (e.g. cash to bank). On top of that, I want it to check for the month and year, but not the day.

What I ended up with was an insane and convoluted formula:

Formula:
`Please Login or Register  to view this content.`

Basically, this thing just adds up all the income items, and subtracts from them the items I don't want. Is there a simpler way to do this?!

I know there are ways to combine SUMIF and SUMPRODUCT, but I just can't wrap my head around it. Any help would be appreciated.

Thanks!

2. ## Re: Looking for something simpler than SUMIFS - SUMIFS

can you summarise using a pivot table at all

perhaps a sample spreadsheet attached may help

3. ## Re: Looking for something simpler than SUMIFS - SUMIFS

Originally Posted by etaf
can you summarise using a pivot table at all

perhaps a sample spreadsheet attached may help
Pivot table works fine, but I just don't like the way it looks and the fact that it doesn't auto refresh until you reopen the file.

4. ## Re: Looking for something simpler than SUMIFS - SUMIFS

Still long...

=SUMIFS('Income & Expenses'!M:M,'Income & Expenses'!E:E,"I",'Income & Expenses'!C:C,MONTH(B4),'Income & Expenses'!D:D,YEAR(B4))-SUM(SUMIFS('Income & Expenses'!M:M,'Income & Expenses'!I:I,{"Local Reimbursement","Offshore Reimbursement","Interaccount In"},'Income & Expenses'!D:D,YEAR(B4),'Income & Expenses'!C:C,MONTH(B4)))

5. ## Re: Looking for something simpler than SUMIFS - SUMIFS

Originally Posted by Tony Valko
Still long...

=SUMIFS('Income & Expenses'!M:M,'Income & Expenses'!E:E,"I",'Income & Expenses'!C:C,MONTH(B4),'Income & Expenses'!D:D,YEAR(B4))-SUM(SUMIFS('Income & Expenses'!M:M,'Income & Expenses'!I:I,{"Local Reimbursement","Offshore Reimbursement","Interaccount In"},'Income & Expenses'!D:D,YEAR(B4),'Income & Expenses'!C:C,MONTH(B4)))
Oh, nice. That {} thing acts as OR, I guess? Thanks much simpler now.

6. ## Re: Looking for something simpler than SUMIFS - SUMIFS

``Please Login or Register  to view this content.``
You can easy refresh your pivot table.

Excel 2007 => data => refresh pivot table.

7. ## Re: Looking for something simpler than SUMIFS - SUMIFS

Originally Posted by YAbdelaal
Oh, nice. That {} thing acts as OR, I guess?
Yes!

Thanks much simpler now.
You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

8. ## Re: Looking for something simpler than SUMIFS - SUMIFS

Thanks, everyone!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1