SUMPRODUCT
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.
|