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 |
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 |
Conditional sum on an array based on another array
Would a SUMIF do it?
=SUMIF(A2:L2,"",A3:L3) |
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) |
All times are GMT +1. The time now is 01:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com