SUMPRODUCT

[Sho]

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?

[Domenic]

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$11)

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

Hope this helps!

In article <[email protected]>,
Sho <[email protected]> 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)*(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=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")*Sheet2!$D$2:$D$11),0)
>
>
> Any ideas?

[Harlan Grove]

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$11)
....

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)

[Domenic]

In article <[email protected]>,
"Harlan Grove" <[email protected]> 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$11)
> ...
>
> 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?

[Harlan Grove]

Domenic wrote...
>"Harlan Grove" <[email protected]> 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$11)
....
>>=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.

[Domenic]

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 <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> Domenic wrote...
> >"Harlan Grove" <[email protected]> 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$11)
> ...
> >>=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.