PDA

View Full Version : Solved: SUMPRODUCT across multiple sheets



Lawrence
08-19-2008, 11:33 AM
I have the following formula reading from data sheet 2008.04a.

=SUMPRODUCT(--(DAY('2008.04a'!$F$2:$F$65536)=DAY($A6)), (--('2008.04a'!$R$2:$R$65536=$B$2)), '2008.04a'!$AF$2:$AF$65536)

'2008.04a'!$F$2:$F$65536 contains the dates to match with the day in $A6
'2008.04a'!$R$2:$R$65536 contains the employee name to match with $B$2
Then once these two conditions are met, the sum is returned.

Due to its size, my data is broken down into 2 sheets and I would like to extend this formula to 2008.04b. I have done the following (a bit ugly), but it only works for the first cell and when I copy it down, it returns zeros.

=IF(DAY('2008.04a'!$F$2:$F$65536)=DAY($A6), SUMPRODUCT(--(DAY('2008.04a'!$F$2:$F$65536)=DAY($A6)), (--('2008.04a'!$R$2:$R$65536=$B$2)), '2008.04a'!$AF$2:$AF$65536), SUMPRODUCT(--(DAY('2008.04b'!$F$2:$F$65536)=DAY($A6)), (--('2008.04b'!$R$2:$R$65536=$B$2)), '2008.04b'!$AF$2:$AF$65536))

Bob Phillips
08-19-2008, 12:08 PM
Why not just use

=SUMPRODUCT(--(DAY('2008.04a'!$F$2:$F$65536)=DAY($A6)), (--('2008.04a'!$R$2:$R$65536=$B$2)), '2008.04a'!$AF$2:$AF$65536)
+SUMPRODUCT(--(DAY('2008.04b'!$F$2:$F$65536)=DAY($A6)), (--('2008.04b'!$R$2:$R$65536=$B$2)), '2008.04b'!$AF$2:$AF$65536)

Lawrence
08-19-2008, 12:48 PM
Why not just use
Because I am not as smart as you. ;)