Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with varying column ranges
I am trying to create a formula that gives me the following result from the deprec sheet below into field "x" in the current sheet: i am selecting "wh1" from column "a" and the month of "31/01/06" from column "b" in the current sheet, and then i am using an array to call up the sum in the deprec sheet column "e,f,g" after filtering the same month from column "b,c,d" and the location "wh1" found in column "a". CURRENT SHEET A B C 1 DATE WH1 WH2 2 31/01/06 X 3 29/02/06 4 31/03/06 DEPREC SHEET A B C D E F G 1 WH1 31/12/05 31/01/06 29/02/06 45 66 22 2 WH2 31/03/07 30/04/07 22/05/07 22 87 92 3 FG3 31/03/06 30/04/06 05/05/06 74 30 41 X=(SUM(IF(DEPREC!$A$1:$A$3=B$1,IF(DEPREC!$B$1:$D$3 =DATE(YEAR($A2),MONTH($A2),1),DEPREC!$E$1:$G$3),0 )))-(SUM(IF(DEPREC!$A$1:$A$3=B$1,IF(DEPREC!$B$1:$D$3D ATE(YEAR($A2),MONTH($A2)+1,0),DEPREC!$E$1:$G$3),0) )) OR X=(SUMPRODUCT(--(DEPREC!$A$1:$A$3=$B$1),--(DEPREC!$B$1:$D$3=DATE(YEAR($A2),MONTH($A2),1)),D EPREC!$E$1:$G$3))-(SUMPRODUCT(--(DEPREC!$A$1:$A$3=$B$1),--(DEPREC!$B$1:$D$3DATE(YEAR($A2),MONTH($A2)+1,0)), DEPREC!$E$1:$G$3)) OR X=(SUMPRODUCT((DEPREC!$A$1:$A$3=$B$1)*(DEPREC!$B$1 :$D$3=DATE(YEAR($A2),MONTH($A2),1))*(DEPREC!$E$1: $G$3)))-(SUMPRODUCT((DEPREC!$A$1:$A$3=$B$1)*(DEPREC!$B$1:$ D$3DATE(YEAR($A2),MONTH($A2)*1,0))*(DEPREC!$E$1:$ G$3))) I am always getting "#value"... please help.. thnx rami |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct with varying column ranges
rami,
Perhaps: =SUMPRODUCT((Deprec!$A$1:$A$3=$B$1)*(((MONTH(Depre c!$B$1:$B$3)=MONTH($A2))*(Deprec!$E$1:$E$3))+((MON TH(Deprec!$C$1:$C$3)=MONTH($A2))*(Deprec!$F$1:$F$3 ))+((MONTH(Deprec!$D$1:$D$3)=MONTH($A2))*(Deprec!$ G$1:$G$3)))) HTH, Bernie MS Excel MVP "rami" wrote in message ... I am trying to create a formula that gives me the following result from the deprec sheet below into field "x" in the current sheet: i am selecting "wh1" from column "a" and the month of "31/01/06" from column "b" in the current sheet, and then i am using an array to call up the sum in the deprec sheet column "e,f,g" after filtering the same month from column "b,c,d" and the location "wh1" found in column "a". CURRENT SHEET A B C 1 DATE WH1 WH2 2 31/01/06 X 3 29/02/06 4 31/03/06 DEPREC SHEET A B C D E F G 1 WH1 31/12/05 31/01/06 29/02/06 45 66 22 2 WH2 31/03/07 30/04/07 22/05/07 22 87 92 3 FG3 31/03/06 30/04/06 05/05/06 74 30 41 X=(SUM(IF(DEPREC!$A$1:$A$3=B$1,IF(DEPREC!$B$1:$D$3 =DATE(YEAR($A2),MONTH($A2),1),DEPREC!$E$1:$G$3),0 )))-(SUM(IF(DEPREC!$A$1:$A$3=B$1,IF(DEPREC!$B$1:$D$3D ATE(YEAR($A2),MONTH($A2)+1,0),DEPREC!$E$1:$G$3),0) )) OR X=(SUMPRODUCT(--(DEPREC!$A$1:$A$3=$B$1),--(DEPREC!$B$1:$D$3=DATE(YEAR($A2),MONTH($A2),1)),D EPREC!$E$1:$G$3))-(SUMPRODUCT(--(DEPREC!$A$1:$A$3=$B$1),--(DEPREC!$B$1:$D$3DATE(YEAR($A2),MONTH($A2)+1,0)), DEPREC!$E$1:$G$3)) OR X=(SUMPRODUCT((DEPREC!$A$1:$A$3=$B$1)*(DEPREC!$B$1 :$D$3=DATE(YEAR($A2),MONTH($A2),1))*(DEPREC!$E$1: $G$3)))-(SUMPRODUCT((DEPREC!$A$1:$A$3=$B$1)*(DEPREC!$B$1:$ D$3DATE(YEAR($A2),MONTH($A2)*1,0))*(DEPREC!$E$1:$ G$3))) I am always getting "#value"... please help.. thnx rami |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation for varying ranges | Excel Worksheet Functions | |||
Using macro/vba to copy varying ranges of rows | Excel Discussion (Misc queries) | |||
Can SUMPRODUCT be used to extract varying data in a column? | Excel Worksheet Functions | |||
SUMPRODUCT with varying # of rows | Excel Worksheet Functions | |||
How do I chart date ranges with varying start and finish dates? | Charts and Charting in Excel |