Conditional sum on an array based on another array

[drjayr2002]

here is the data:
Mon 01 02 03 04 05 06 07 08 09 10 11 12
Actual 10 23 40 50 65
Fcst 8 20 44 55 60 70 55 55 50 40 30 20
The actuals are entered on a month end.
I need a formula to check the 'Actual' row for 0, and take the index and
create a dynamic array of Fcst Row and get a sum of the forecast.

In the above example, the actual is zero for 06, and need sum of Fcst row(
06:12), This will change for next month when actuals are entered for June.

Help is apprecited much
-- Jay

 

[RagDyer]

Try this *array* formula:

=SUM(M3:INDEX(B2:M2,MATCH(TRUE,(B2:M2=0),0)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.


This assumes that you fill the values in the actual row in order, and there
are no blanks other then the future, uncompleted months.

 

[Dave O]

Would a SUMIF do it?
=SUMIF(A2:L2,"",A3:L3)

 

[RagDyer]

Talk about *overcomplicated* ! ! !<vbg>