Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Question Using SUMIFS when the Range & Criteria rely on there relationship

    I am trying to use SUMIFS to sum difference between 2 cells in a range when the preceding range is less than the range from which I calculate the sum

    To try and make more sense of this I have prepared THE ATTACHED table.





    In the SUMIFS function the difference between column C and B is what I want to sum conditional on C1>B1,C2>B2 etc. I have tried as the criteria if C1>B1 for each element but that does not work as the criteria range has to be the same as the sum range.

    Any suggestions greatly appreciated .Know I could do this longhand but the result fits into a larger formal.

    Thanks

    Peter
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by mitchbvi; 2013-04-05 at 14:25. Reason: TABLE DID NOT SHOW

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    From your illustration, it seems like it's a MAX function rather than a SUMIFS.

    Clip0001.jpg

    Then, you can do a =SUM(range) at the bottom of that column.
    Last edited by kweaver; 2013-04-05 at 15:04.

  3. The Following User Says Thank You to kweaver For This Useful Post:

    mitchbvi (2013-04-05)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks for taking the time to reply. It is not a max solution as what I want this to do for me in the scheme of things is test a series of possibilities. As I mentioned I could get the result with a series of columns/rows but that clutters the worksheet. I cannot do so by just testing the 2 ranges as the first could in total be higher than the second but one cell in the second could be higher than the corresponding cell in the first column.

    So the Sumifs on column C adds those cells that are higher than the corresponding cell in Column B having deducted the value in the cells in B. In the example that results in 400 which is the test I want to achieve.
    Last edited by mitchbvi; 2013-04-05 at 15:26. Reason: bad grammar

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm not sure why my formula filled down from D3 to D6 isn't what you want. It's the difference between C and B cols (C3-B3, for example) and if the difference is negative, the result is 0. Then, in my illustration, in D7 I have =SUM(D3 : D6). Of course, this can all be adjusted for more rows of data.

    Kevin

  6. #5
    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
    Perhaps
    =sumproduct((range2>range1)*(range2-range1))
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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