| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional sum on an array based on another array
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional sum on an array based on another array
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "drjayr2002" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional sum on an array based on another array
Would a SUMIF do it?
=SUMIF(A2:L2,"",A3:L3) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional sum on an array based on another array
Talk about *overcomplicated* ! ! !<vbg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave O" wrote in message ... Would a SUMIF do it? =SUMIF(A2:L2,"",A3:L3) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array: Counting multiple values within array | Excel Worksheet Functions | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
| Conditional formatting based upon array | Excel Worksheet Functions | |||
| Goal Seek On Members of an Array within Array | Excel Worksheet Functions | |||
Linear Mode
