+ Reply to Thread
Results 1 to 8 of 8

Looking for something simpler than SUMIFS - SUMIFS

  1. #1
    Registered User
    Join Date
    05-26-2014
    Posts
    14

    Question 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: copy to clipboard
    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. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,524

    Re: Looking for something simpler than SUMIFS - SUMIFS

    can you summarise using a pivot table at all

    perhaps a sample spreadsheet attached may help
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-26-2014
    Posts
    14

    Re: Looking for something simpler than SUMIFS - SUMIFS

    Quote Originally Posted by etaf View Post
    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. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    05-26-2014
    Posts
    14

    Re: Looking for something simpler than SUMIFS - SUMIFS

    Quote Originally Posted by Tony Valko View Post
    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. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    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.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking for something simpler than SUMIFS - SUMIFS

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

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


    If your question has been solved please mark the thread as being solved.

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

  8. #8
    Registered User
    Join Date
    05-26-2014
    Posts
    14

    Re: Looking for something simpler than SUMIFS - SUMIFS

    Thanks, everyone!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 09-29-2013, 04:50 AM
  2. [SOLVED] Sumifs
    By crak in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-12-2013, 12:41 AM
  3. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  4. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  5. Simpler solutions than sumifs
    By inayat in forum Excel General
    Replies: 12
    Last Post: 08-08-2011, 05:59 AM

Bookmarks

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