Question about summing based on relative cell references

[silverbullitt]

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

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.

 

[Fazza]

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

 

[silverbullitt]

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))

 

[silverbullitt]

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

[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

 

[Fazza]

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

 

[Br0ck]

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

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

 

[silverbullitt]

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.

 

[silverbullitt]

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

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

 

[Br0ck]

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.

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

 

[Fazza]

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