# Thread: Using SUMIFS when the Range & Criteria rely on there relationship

1. ## 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

2. 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.

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

mitchbvi (2013-04-05)

4. 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.

5. 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. Perhaps
=sumproduct((range2>range1)*(range2-range1))

#### Posting Permissions

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