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

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