Try...

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)*(ISNUM BER(MATCH(Sheet2!$C$3:$
C$11,{"INT","ACC","CCT"},0)))*(Sheet2!$G$3:$G$11=S heet1!A1),Sheet2!$D$3:$
D$11)

Note that the ranges need to be the same size. Therefore, adjust them
accordingly.

Hope this helps!

In article ,
Sho wrote:

I have the following formula which adds column D based on criteria in column
C. I now want to amend this so that it performs this calculation if the
values in Sheet 2 cells G2:G11=Sheet1!A1.

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)*(Sheet 2!$C$3:$C$11="INT")*Sheet2!$
D$2:$D$11)+SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A 14)*(Sheet2!$C$3:$C$11="ACC"
)*Sheet2!$D$2:$D$11)+SUMPRODUCT((Sheet2!$B$3:$B$11 =Sheet1!$A14)*(Sheet2!$C$3:$
C$11="CCT")*Sheet2!$D$2:$D$11)

I have tried the following formula which works on the first cell but when I
autofill this down to apply the formula to look at cells A15, A16 etc I do
not get any values.

=IF(Sheet2!$G$2:$G$11=Sheet1!$A$1,SUMPRODUCT((Shee t2!$B$3:$B$11=Sheet1!$A14)*(
Sheet2!$C$3:$C$11="INT")*Sheet2!$D$2:$D$11)+SUMPRO DUCT((Sheet2!$B$3:$B$11=Shee
t1!$A14)*(Sheet2!$C$3:$C$11="ACC")*Sheet2!$D$2:$D$ 11)+SUMPRODUCT((Sheet2!$B$3:
$B$11=Sheet1!$A14)*(Sheet2!$C$3:$C$11="CCT")*Sheet 2!$D$2:$D$11),0)


Any ideas?