|
SUMPRODUCT
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? |
SUMPRODUCT
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? |
SUMPRODUCT
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) |
SUMPRODUCT
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? |
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. |
SUMPRODUCT
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. |
| All times are GMT +1. The time now is 10:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com