Need help with a SUM function!!

[peshopesh]

I have a budget spreadhseet which has a column for estimated spend, and a column for actual spend.

I have a SUM function set up to calculate the total 'actual spend' but if the actual is missing, I want the SUM function to refer to the 'estimated spend' column.

Is this possible, I have been looking into it for over a day now.

Any help would be greatly appreciated!

This is my first post so be gentle!

Pesh

 

[Richard Schollar]

Hi Pesh

So your sum might be a combination of the two columns? Say you have data in A2:B5 (col A is actuals, B is Estimate) you could use this:

=SUMPRODUCT((A2:A5="")*B2:B5+(A2:A5< >"")*A2:A5)

 

[Peter_SSs]

Welcome to the MrExcel board!

Is it really only 1 column of each, or do you have these two columns for each month maybe?

It would be very helpful if we could get a better idea of your layout and data. Would it be possible to post a small screen shot using one of the add-ins below and explain your requirements in relation to the screen shot?
Excel jeanie
or
Colo's HTML Maker

 

[peshopesh]

WOW!

You guys are not only fast, but pretty good as well! Cheers Richard, that worked fine, and appears to be the right method. Is this possible to be made shorter or simplified?

Cheers Peter for your reply as well, I will use some of those add-ins in future. What are the best add-ins available for Excel, could you PM them to me if this is not the suitable place to discuss.

Pesh

 

[Richard Schollar]

I guess you could use

=SUM(IF(A2:A5,A2:A5,B2:B5))

which is an array formula and so must be confirmed with Ctrl+Shift+Enter (Excel will surround with curly braces {} on successful entry - don't try and enter these manually yourself).

 

[Peter_SSs]

.. or this non-array formula?
=SUM(A2:A5)+SUMIF(A2:A5,"",B2:B5)

Richard
I think that last one fails if there is an actual spend of 0 in a row.