# Question about summing based on relative cell references

#### silverbullitt

##### New Member
I have two ranges of cells, and want to sum the lowest of the two values. So, I want to sum two columns, like the example below. Blanks are intentional
Code:
``````Budget   Actual
100
150
200        50
100        40
80
70         100``````

I want to determine a formula to return a sum of 520 (100+150+50+40+80+100)- it picks the value in column 2 if there is one, otherwise it picks the value in the first column. I'm not an excel pro, so sorry if this is easy. I've been looking at sumif but it's got me all kinds of confused.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
hi,

This solution excludes column A values when the column B value is greater than zero. This may not be OK for your data.

=SUM(A2:B7)-SUMIF(B2:B7,">0",A2:A7)

regards

So, this formula gives me the answer I want, but how do I extend it to the whole range?

=SUM(IF(ISBLANK(I4),H4,I4))

hi,

This solution excludes column A values when the column B value is greater than zero. This may not be OK for your data.

=SUM(A2:B7)-SUMIF(B2:B7,">0",A2:A7)

regards

Thanks!!

That formula, when applied to the ranges in my sheet, gives me \$809.25. The formula

Code:
``````[TABLE="width: 255"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Budget[/TD]
[TD]Actual Cost[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]\$200.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]\$100.00[/TD]
[TD]\$91.30[/TD]
[/TR]
[TR]
[TD]\$2,000.00[/TD]
[TD]\$416.00[/TD]
[/TR]
[TR]
[TD]\$150.00[/TD]
[TD]\$101.95[/TD]
[/TR]
[TR]
[TD]\$50.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]\$100.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]\$100.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]\$20.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]\$300.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]\$250.00
\$100.00[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]``````

The way I'm thinking about this (but perhaps not elaborating), the formula I want would give me \$1729.25

ADVERTISEMENT
OK, I'm getting lost.

Did you check my formula on your original data? It gave me the expected 520

Preferably confirm this before changing the data.

For the second data set I get 1,379.25
If that is not what you want, please explicitly detail the result desired

regards

Per your provided data, I get your wanted value by using the formula...

Code:
``=SUM(A2:B12)-SUMIF(B2:B5,">0",A2:A12)``

ADVERTISEMENT
OK, I'm getting lost.

Did you check my formula on your original data? It gave me the expected 520

Preferably confirm this before changing the data.

For the second data set I get 1,379.25
If that is not what you want, please explicitly detail the result desired

regards

I did, and it gave me the values you just posted. What I want is the sum of the first column in all cells where there is not a value in the second column, and where there is a value in the second column, to add that. So, to sum all values in column 1 where column 2 is blank, plus all values in column 2.

Last edited:
Per your provided data, I get your wanted value by using the formula...

Code:
``=SUM(A2:B12)-SUMIF(B2:B5,">0",A2:A12)``

I have no clue what that does... Adds all values, then subtracts A2:A12 because...? I couldn't find an example on the internet that featured that type of reference. I could do this in a macro, but I don't want to do a macro-enabled sheet for compatibility reasons.

The code works for how we are interpreting your request. You wanted \$1729.25 and the code does that. Sorry that we are not understanding your request.

What the code is doing is getting the sum of Column A where there is no value in Column B (skipping \$100, \$2000, and \$150) and adding the sum of Column B. A2:A12 is not being subtracted, it is a SUMIF argument asking for the actual values to sum based on the SUMIF arguments.

I was changing the range of the formula that Fazza gave to cover the data you provided. Actually, the SUMIF range should have been B2:B12.

Code:
``=SUM(A2:B12)-SUMIF(B2:B12,">0",A2:A12)``

What I want is the sum of the first column in all cells where there is not a value in the second column, and where there is a value in the second column, to add that. So, to sum all values in column 1 where column 2 is blank, plus all values in column 2.

The requirement is clear, thank you.

The formula I offered gets the same result but with a slightly different approach.

It adds all values in both columns [the =SUM(A2:Bn) part] and then subtracts the unwanted values. That is the A column values where there is a B column value. The means used to do this may not be correct in all cases for your data.

-SUMIF(B2:Bn,">0",A2:An)

The SUMIF says sum from column A where the value in column B is greater than zero. Maybe this ">0" test is not good in all cases for your data. But if it is OK, it is a handy way to do what is wanted without using an array formula.

Is that clear? Is it OK?

PS. Not sure if this extra comment is needed. Referring to the original sample data, the formula I gave adds ALL values, then subtracts the sum of 200+100+70

Last edited:

Replies
20
Views
743
Replies
4
Views
259
Replies
1
Views
954
Replies
8
Views
2K
Replies
13
Views
924

Threads
1,196,263
Messages
6,014,311
Members
441,814
Latest member
youngstubbs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back