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

    Sumifs within a range

    Help!

    In one column I need a sumifs formula that will add elements 330-334
    In the next column I need a sumifs formula that will add elements 335-339

    I have tried using =SUMIFS($M$2:$M$11,$D$2:$D$11,"=>330*",$D$2:$D$11, "=<334",$B$2:$B$11,$A16)
    I have also tried =SUMIFS($M$2:$M$11,$D$2:$D$11,"=>335*",$D$2:$D$11, "=<339",$B$2:$B$11,$A15)

    Both formulas gave me zero

    Anybody no how to negotiate this?


    Attachment of sample file: Sumifs within a range.xlsx

  2. #2
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Oops. know not no

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

    I think the problem is that you are comparing TEXT values vs Numbers in your criteria for the SumIfs.
    Here's formulas that will work around the problem.
    Grants: =SUMIFS($M$2:$M$11,$D$2:$D$11,">229*",$D$2:$D$11," <334*",$B$2:$B$11,$A15)
    Intergv'tl: =SUMIFS($M$2:$M$11,$D$2:$D$11,">334*",$D$2:$D$11," <339*",$B$2:$B$11,$A15)
    Attached Files Attached Files
    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-19)

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You also had an issue of using =< and => rather than >= and <=

    =SUMIFS($M$2:$M$11,$D$2:$D$11,">=330*",$D$2:$D$11, "<=334*",$B$2:$B$11,$A15)
    Regards,
    Rory

    Microsoft MVP - Excel

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

    DGStewart (2012-09-19)

  7. #5
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts
    That worked perfectly but I don't understand why. I understand >229 but <334 would make me think that I wouldn't pull in any 334's. Additionally, >334 makes sense but <339 seems like I would miss the 339's. Of course when I changed a cell to test it computes just fine. Really thought I would need those "equal to" signs in there but I don't. Super happy this worked but maybe you could explain the why behind it?

  8. #6
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for the tip. I added the = signs as you mentioned into RG's formula and the answer was good. Oddly enough, it didn't seem to matter whether they were present in the formula.

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

    The = signs didn't matter because your sample data set did not contain the values on the edges of the range, i.e. 329. However if you do include those values in the data set the results will change incorrectly! Note on the other end it is 334* and w/o the * it will not include the 334! Remember when setting up formulas like this to use data to test the upper & lower range to make sure you have it coded correctly. Thus you should have values in your test data set for 333,334,335 and 329,330,331 to make sure only the correct ones are selected.
    Last edited by RetiredGeek; 2012-09-19 at 12:25.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    DGStewart (2012-09-19)

Posting Permissions

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