sumproduct with varying column ranges

[rami]

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$3>DATE(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)),DEPREC!$E$1:$G$3))-(SUMPRODUCT(--(DEPREC!$A$1:$A$3=$B$1),--(DEPREC!$B$1:$D$3>DATE(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$3>DATE(YEAR($A2),MONTH($A2)*1,0))*(DEPREC!$E$1:$G$3)))


I am always getting "#value"... please help.. thnx

rami

 

[Bernie Deitrick]

rami,

Perhaps:

=SUMPRODUCT((Deprec!$A$1:$A$3=$B$1)*(((MONTH(Deprec!$B$1:$B$3)=MONTH($A2))*(Deprec!$E$1:$E$3))+((MONTH(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