PDA

View Full Version : Sumproduct help



khalid79m
01-21-2009, 05:52 AM
=SUMPRODUCT(--(Data1!$C$3:$C$65536="CCO List")*(Data1!$D$3:$D$65536=$B4))

my formula is above,

currently in the formula I select Data1! as the sheet, I want to use a cell refernce for the sheet name eg



=SUMPRODUCT(--(a2!$C$3:$C$65536="CCO List")*(a2!$D$3:$D$65536=$B4))

i tried this and it didnt work :( can anyoneassist ?

mdmackillop
01-21-2009, 06:28 AM
You will need to use the INDIRECT function to get the sheet name from A2

xld
01-21-2009, 06:33 AM
You don't need -- and *

=SUMPRODUCT(--(INDIRECT("'"&A1&"'!$C$3:$C$65536")="CCO List"),
--(INDIRECT("'"&A1&"'!$D$3:$D$65536")=$B4))