sumproduct with varying column ranges

  • Thread starter Thread starter rami
  • Start date Start date
R

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
 
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
 
Back
Top