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?

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?

Domenic wrote...
Try...

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*(ISNUMBER(MATCH(Sheet2!$C$3:$C$11,{"INT","ACC"," CCT"},0)))
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$1 1)
....

ISNUMBER(MATCH(..)) would only be needed when Sheet2!D3:D11 could
contain text. If that range would always contain numbers or blank
cells, the formula could be simplified to

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

If the codes "INT", "ACC", and "CCT" were entered in a range named
CODES, the formula could be rewritten as

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*COUNTIF(Codes,Sheet2!$C$3:$C$11)
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$11)

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
Try...

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*(ISNUMBER(MATCH(Sheet2!$C$3:$C$11,{"INT","ACC"," CCT"},0)))
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$1 1)
...

ISNUMBER(MATCH(..)) would only be needed when Sheet2!D3:D11 could
contain text. If that range would always contain numbers or blank
cells, the formula could be simplified to

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

Thanks Harlan! But isn't ISNUMBER/MATCH more efficient than
={"INT","ACC","CCT"} ?

If the codes "INT", "ACC", and "CCT" were entered in a range named
CODES, the formula could be rewritten as

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*COUNTIF(Codes,Sheet2!$C$3:$C$11)
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$11)

Nice, Harlan! But which one is more efficient, ISNUMBER/MATCH or
COUNTIF?

Domenic wrote...
"Harlan Grove" wrote:
Domenic wrote...
....
=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*(ISNUMBER(MATCH(Sheet2!$C$3:$C$11,{"INT","ACC" ,"CCT"},0)))
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$1 1)
....
=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*(Sheet2!$C$3:$C$11={"INT","ACC","CCT"})
*(Sheet2!$G$3:$G$11=Sheet1!A1)*Sheet2!$D$3:$D$11 )

Thanks Harlan! But isn't ISNUMBER/MATCH more efficient than
={"INT","ACC","CCT"} ?

Depends. Two function calls aren't free, and there's also the boolean
to numeric conversion.

If the codes "INT", "ACC", and "CCT" were entered in a range named
CODES, the formula could be rewritten as

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*COUNTIF(Codes,Sheet2!$C$3:$C$11)
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$11 )

Nice, Harlan! But which one is more efficient, ISNUMBER/MATCH or
COUNTIF?

One function call returning numbers vs two function calls returning
booleans that need to be converted to numbers.

I benchmarked the recalc speeds. Your formula does recalculate faster
than either of mine when there are relatively many matches in the range.

Thanks Harlan! It confirms what I understood to be true -- that
ISNUMBER/MATCH is more efficient. It's also my understanding that using
the double negative to coerce TRUE/FALSE is more efficient.

For these reasons, I prefer to use both the comma syntax and
ISNUMBER/MATCH...

=SUMPRODUCT(--(Sheet2!$B$3:$B$11=Sheet1!$A14),--(ISNUMBER(MATCH(Sheet2!$C
$3:$C$11,{"INT","ACC","CCT"},0))),--(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!
$D$3:$D$11)

There's also another reason -- and don't laugh! :) It's because of
aesthetics. I don't particularly like the look of the end bit when
using the star syntax...

)*Sheet2!$D$3:$D$11)

Okay, you can laugh. :) But, yes, I do realize that unless there's a
large number of matches in the range, the difference in speed won't be
apparent, or any difference in speed will be negligible.

In article .com,
"Harlan Grove" wrote:

Domenic wrote...
"Harlan Grove" wrote:
Domenic wrote...
...
=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*(ISNUMBER(MATCH(Sheet2!$C$3:$C$11,{"INT","ACC" ,"CCT"},0)))
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$1 1)
...
=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*(Sheet2!$C$3:$C$11={"INT","ACC","CCT"})
*(Sheet2!$G$3:$G$11=Sheet1!A1)*Sheet2!$D$3:$D$11 )

Thanks Harlan! But isn't ISNUMBER/MATCH more efficient than
={"INT","ACC","CCT"} ?

Depends. Two function calls aren't free, and there's also the boolean
to numeric conversion.

If the codes "INT", "ACC", and "CCT" were entered in a range named
CODES, the formula could be rewritten as

=SUMPRODUCT((Sheet2!$B$3:$B$11=Sheet1!$A14)
*COUNTIF(Codes,Sheet2!$C$3:$C$11)
*(Sheet2!$G$3:$G$11=Sheet1!A1),Sheet2!$D$3:$D$11 )

Nice, Harlan! But which one is more efficient, ISNUMBER/MATCH or
COUNTIF?

One function call returning numbers vs two function calls returning
booleans that need to be converted to numbers.

I benchmarked the recalc speeds. Your formula does recalculate faster
than either of mine when there are relatively many matches in the range.