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!$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")*Shee t2!$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)+SUMPRODUCT((Sheet2!$B$3:$B$1 1=Sheet1!$A14)*(Sheet2!$C$3:$C$11="ACC")*Sheet2!$D $2:$D$11)+SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A1 4)*(Sheet2!$C$3:$C$11="CCT")*Sheet2!$D$2:$D$11),0)


Any ideas?