G
Guest
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)*(Sheet2!$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")*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((Sheet2!$B$3:$B$11=Sheet1!$A14)*(Sheet2!$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")*Sheet2!$D$2:$D$11),0)
Any ideas?
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)*(Sheet2!$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")*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((Sheet2!$B$3:$B$11=Sheet1!$A14)*(Sheet2!$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")*Sheet2!$D$2:$D$11),0)
Any ideas?