Sumproduct help

[khalid79m]

=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]

You will need to use the INDIRECT function to get the sheet name from A2

[Bob Phillips]

You don't need -- and *

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