Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Sumifs Question using Excel 2010

    I have a budget spreadsheet using sumifs but have not figured out how to exclude certain data to prevent duplicates in my sum. I have attached a sample spreadsheet that should give the basics of what I am trying to accomplish. I want to sum column M when column D is greater than 5* but I want to exclude 597. I tried using "<> 597" in my formula but that didn't work. Column B is department. Attached spreadsheet shows what is being calculated vs. what the answer should be.

    Attachment: Budget with sumifs.xlsx

    Example formula for summing Capital Outlay
    =SUMIFS($M$3:$M$26,$B$3:$B$26,"301",$D$3:$D$26,"=5 *",$D$3:$D$26, "<>597",$F$3:$F$26,"=6*")

    Example formula for summing Debt Service
    =SUMIFS($M$3:$M$26,$B$3:$B$26,"301",$D$3:$D$26,"=5 *",$D$3:$D$26, "<>597",$F$3:$F$26,">=7*",$F$3:$F$26,"<=9*")

    Note that both are including 597 even though I have told it to exclude 597.

    I would also like to combine transfers out with agency transfers but haven't found a way to add items in column F that are in the range of 90-99 and have 597 without running into problems.

  2. #2
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts
    This also does not work.

    =SUMIFS($M$3:$M$26,$B$3:$B$26,"301",$D$3:$D$26,">5 01",$D$3:$D$26, "<597",$F$3:$F$26,"=6*")

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    DG,

    How about this?
    =SUMIFS($M$3:$M$26,$D$3:$D26,"=5*")-SUMIFS($M$3:$M$26,$D$3:$D26,"=597")

    Gives a result of 125,328 which is what selecting the correct cells in col M showes on the status bar.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    DGStewart (2012-09-18)

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can also use something like:
    Example formula for summing Capital Outlay
    =SUMIFS($M$3:$M$26,$B$3:$B$26,"301",$D$3:$D$26,"=5 *",$D$3:$D$26, "<>597*",$F$3:$F$26,"=6*")

    Example formula for summing Debt Service
    =SUMIFS($M$3:$M$26,$B$3:$B$26,"301",$D$3:$D$26,"=5 *",$D$3:$D$26, "<>597*",$F$3:$F$26,">=7*",$F$3:$F$26,"<=9*")


    The problem with your SUMIFS is that the TEXT that looks like numbers in your columns, is not being compared to the TEXT "597" but to the NUMBER 597. The Text values never equal the number value!. With the "5*", etc you explicitly make it a text comparison, so you need to make the "597" a text comparison as well. Adding an asterisk does this.

    Steve
    Last edited by sdckapr; 2012-09-19 at 05:54. Reason: Some of Post got deleted

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    DGStewart (2012-09-18)

  7. #5
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I couldn't get this one to work. Missing argument. I was able to subtract sumifs as mentioned by RetiredGeek. Thanks to both of you. I do appreciate your help. New sumifs question on its way.

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Sorry about that. Some of the post was truncated for some reason. I have fixed it...

    Steve

Posting Permissions

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