formulas needed .. thanks

[nikko]

Hi, pls advise if there's any formulas that can help to derive below

($) Sales Forecast, Estimate Rev, Actual Rev
Sales Off 1 - Jan
Sales Off 1 - Feb
Sales Off 2 - Jan
SAles Off 2 - Feb
Sales Off 3 - Jan
Sales Off 3 - Feb

I'm trying to sum-up the Jan, Feb and Mar data for All sales offices by
using the Actual Rev. If Actual Rev is blank, then use estimated Rev. If
estimated rev is blank, then use sales forecast nos.

Any help will be appreciated.. thanks!

 

[Bob Phillips]

=SUM(IF(B2:B200="",A2:A200, B2:B200))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[nikko]

Hi Bob,
Say if Col B is for Actual rev, Col A is for Estimate rev, then what about
the sales forecast column...

 

[Bob Phillips]

Don't understand the question.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Brad]

There are more elegant ways to do this

E1 = Actual
D1 = Estimated
C1 = Sales
=IF(ISNUMBER(E1),E1,IF(ISNUMBER(D1),D1,C1))

In F1 and then use a sumproduct similiar to what Bob Phillips suggested.