| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace SUMIF with SUMPRODUCT
I would like to replace the formula below with SUMPRODUCT:
=SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M: \WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A $70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70)) Thanks in advance for your assitance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
=SUMPRODUCT((('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="IN T")+('M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A$70="MA")),'M:\WORKBOOK1\SHEET1'!"$A K$7:$AK$70)) UNTESTED best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "torooo" wrote in message ... I would like to replace the formula below with SUMPRODUCT: =SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M: \WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A $70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70)) Thanks in advance for your assitance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
There is an extra parenthesis at the end. Other than that, that's the same as
what I got. Just remember to enter it as an array. (Ctrl+Shift+Enter) -- Best Regards, Luke M "Bernard Liengme" wrote: =SUMPRODUCT((('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="IN T")+('M: \WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A$70="MA")),'M:\WORKBOOK1\SHEET1'!"$A K$7:$AK$70)) UNTESTED best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "torooo" wrote in message ... I would like to replace the formula below with SUMPRODUCT: =SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M: \WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A $70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70)) Thanks in advance for your assitance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
Thanks for your assistance, the formula works (without the extra
parenthesis). Best |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
Another way:
=SUMPRODUCT(--(ISNUMBER(MATCH('M:\WORKBOOK1\SHEET1'!$A$7:$A$70,{ "INT","IT","MA"),0))),'M:\WORKBOOK1\SHEET1'!"$AK$7 :$AK$70) -- Biff Microsoft Excel MVP "torooo" wrote in message ... I would like to replace the formula below with SUMPRODUCT: =SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M: \WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A $70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70)) Thanks in advance for your assitance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
Just remember to enter it as an array. (Ctrl+Shift+Enter)
No need to array enter. -- Biff Microsoft Excel MVP "Luke M" wrote in message ... There is an extra parenthesis at the end. Other than that, that's the same as what I got. Just remember to enter it as an array. (Ctrl+Shift+Enter) -- Best Regards, Luke M "Bernard Liengme" wrote: =SUMPRODUCT((('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="IN T")+('M: \WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A$70="MA")),'M:\WORKBOOK1\SHEET1'!"$A K$7:$AK$70)) UNTESTED best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "torooo" wrote in message ... I would like to replace the formula below with SUMPRODUCT: =SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M: \WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A $70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70)) Thanks in advance for your assitance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
Little shorter:
=SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
That will earn a #VALUE! error
Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
If you've seen my posts over the years, you might have noticed my *distaste*
for the unary form of Sumproduct. Besides the fact that it *can't* be used on different sized ranges (column vs. row), and the fact that it allows text numbers to be by-passed without any notice, it *also* doesn't work in this scenario. Just revise your test formula from unary to asterisk, which is the form my suggested formula used. =SUMPRODUCT((A1:A9={"A","B"})*B1:B9) No problem with this ... is there? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bernard Liengme" wrote in message ... That will earn a #VALUE! error Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
Thanks for this, We life and learn
cheers -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... If you've seen my posts over the years, you might have noticed my *distaste* for the unary form of Sumproduct. Besides the fact that it *can't* be used on different sized ranges (column vs. row), and the fact that it allows text numbers to be by-passed without any notice, it *also* doesn't work in this scenario. Just revise your test formula from unary to asterisk, which is the form my suggested formula used. =SUMPRODUCT((A1:A9={"A","B"})*B1:B9) No problem with this ... is there? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bernard Liengme" wrote in message ... That will earn a #VALUE! error Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
calculation times screencap:
http://img267.imageshack.us/img267/6...ltimes1rq2.jpg The multiplication version is slightly faster when the number of variables is small. As the number of variables increases the calc time also increases "signifcantly". The --(ISNUMBER(MATCH(...)) version is faster in 2 out of 3 tests. Also note that the number of variables seems to not slow this version down (although the number of variables is small). Calc times measured using Charles Williams RangeTimer method. -- Biff Microsoft Excel MVP "Bernard Liengme" wrote in message ... Thanks for this, We life and learn cheers -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... If you've seen my posts over the years, you might have noticed my *distaste* for the unary form of Sumproduct. Besides the fact that it *can't* be used on different sized ranges (column vs. row), and the fact that it allows text numbers to be by-passed without any notice, it *also* doesn't work in this scenario. Just revise your test formula from unary to asterisk, which is the form my suggested formula used. =SUMPRODUCT((A1:A9={"A","B"})*B1:B9) No problem with this ... is there? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bernard Liengme" wrote in message ... That will earn a #VALUE! error Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
Bernard, your post got me thinking ... about the uneven 18 element and 9
elements arrays. FWIW, Actually, looking closely at the arrays displayed when <F9 is hit, you see only 9 semi-colons in *both* arrays, where A1 to A9 also shows 9 commas. This means that both arrays are 9 rows in size, while Col A is 9 rows by 2 columns. So, multiplying row times row returns the answers for the multiplication operator. I don't know what happens with the comma in the unary version. If you replace the comma with an asterisk, forcing a second multiplication (unary performing the first), you get the same correct return. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bernard Liengme" wrote in message ... Thanks for this, We life and learn cheers -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... If you've seen my posts over the years, you might have noticed my *distaste* for the unary form of Sumproduct. Besides the fact that it *can't* be used on different sized ranges (column vs. row), and the fact that it allows text numbers to be by-passed without any notice, it *also* doesn't work in this scenario. Just revise your test formula from unary to asterisk, which is the form my suggested formula used. =SUMPRODUCT((A1:A9={"A","B"})*B1:B9) No problem with this ... is there? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bernard Liengme" wrote in message ... That will earn a #VALUE! error Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD ------------------------------------------------------------------------ --- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------ --- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
So Biff, what time fragments do those numbers represent?
Are we talking minutes, seconds, milliseconds ... ? What would those results equate to the OP's scenario? I have personally saved almost 50% in opening and recalc times (25 down to 14) for large WBs being used as databases simply by changing double lookups and Vlookups (w/error traps) to Index and Match formulas, so this is *not* a confrontational question. Are there really *appreciable* savings between the 2 versions? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... calculation times screencap: http://img267.imageshack.us/img267/6...ltimes1rq2.jpg The multiplication version is slightly faster when the number of variables is small. As the number of variables increases the calc time also increases "signifcantly". The --(ISNUMBER(MATCH(...)) version is faster in 2 out of 3 tests. Also note that the number of variables seems to not slow this version down (although the number of variables is small). Calc times measured using Charles Williams RangeTimer method. -- Biff Microsoft Excel MVP "Bernard Liengme" wrote in message ... Thanks for this, We life and learn cheers -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... If you've seen my posts over the years, you might have noticed my *distaste* for the unary form of Sumproduct. Besides the fact that it *can't* be used on different sized ranges (column vs. row), and the fact that it allows text numbers to be by-passed without any notice, it *also* doesn't work in this scenario. Just revise your test formula from unary to asterisk, which is the form my suggested formula used. =SUMPRODUCT((A1:A9={"A","B"})*B1:B9) No problem with this ... is there? -- Regards, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Bernard Liengme" wrote in message ... That will earn a #VALUE! error Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD ----------------------------------------------------------------------- ---- Please keep all correspondence within the NewsGroup, so all may benefit ! ----------------------------------------------------------------------- ---- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
Are we talking minutes, seconds, milliseconds ... ?
Milliseconds Every millisecond adds up! Note that those tests are on 1 single formula. If there were 100's or even 1000's of similar formulas the difference could be significant. Granted, most files aren't big enough or so complex that maximum efficiency needs to be a consideration *but* if you get into the habit of being as efficient as possible to the best of ones ability all the time, then you're ahead of the curve. -- Biff Microsoft Excel MVP "Ragdyer" wrote in message ... So Biff, what time fragments do those numbers represent? Are we talking minutes, seconds, milliseconds ... ? What would those results equate to the OP's scenario? I have personally saved almost 50% in opening and recalc times (25 down to 14) for large WBs being used as databases simply by changing double lookups and Vlookups (w/error traps) to Index and Match formulas, so this is *not* a confrontational question. Are there really *appreciable* savings between the 2 versions? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... calculation times screencap: http://img267.imageshack.us/img267/6...ltimes1rq2.jpg The multiplication version is slightly faster when the number of variables is small. As the number of variables increases the calc time also increases "signifcantly". The --(ISNUMBER(MATCH(...)) version is faster in 2 out of 3 tests. Also note that the number of variables seems to not slow this version down (although the number of variables is small). Calc times measured using Charles Williams RangeTimer method. -- Biff Microsoft Excel MVP "Bernard Liengme" wrote in message ... Thanks for this, We life and learn cheers -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... If you've seen my posts over the years, you might have noticed my *distaste* for the unary form of Sumproduct. Besides the fact that it *can't* be used on different sized ranges (column vs. row), and the fact that it allows text numbers to be by-passed without any notice, it *also* doesn't work in this scenario. Just revise your test formula from unary to asterisk, which is the form my suggested formula used. =SUMPRODUCT((A1:A9={"A","B"})*B1:B9) No problem with this ... is there? -- Regards, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Bernard Liengme" wrote in message ... That will earn a #VALUE! error Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD ----------------------------------------------------------------------- ---- Please keep all correspondence within the NewsGroup, so all may benefit ! ----------------------------------------------------------------------- ---- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace SUMIF with SUMPRODUCT
I know exactly what you're talking about.
That database I referred to, where I cut back the open and recalc times, was a 40,000 row X 32 field sheet. That's over one and a quarter million formulas. You can picture what that was like at the start when there were double Vlookups. Being familiar with my experience using that old, large database sheet, and plugging in your numbers, I figure that there's a 17% difference, that's 7½ minutes - over a 44 + minute open, where my original, *bad* open time was an *actual* 24-25 minutes. Even if your times are flawed (evenly flawed), they *still* show that 17% *advantage*. Interesting! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... Are we talking minutes, seconds, milliseconds ... ? Milliseconds Every millisecond adds up! Note that those tests are on 1 single formula. If there were 100's or even 1000's of similar formulas the difference could be significant. Granted, most files aren't big enough or so complex that maximum efficiency needs to be a consideration *but* if you get into the habit of being as efficient as possible to the best of ones ability all the time, then you're ahead of the curve. -- Biff Microsoft Excel MVP "Ragdyer" wrote in message ... So Biff, what time fragments do those numbers represent? Are we talking minutes, seconds, milliseconds ... ? What would those results equate to the OP's scenario? I have personally saved almost 50% in opening and recalc times (25 down to 14) for large WBs being used as databases simply by changing double lookups and Vlookups (w/error traps) to Index and Match formulas, so this is *not* a confrontational question. Are there really *appreciable* savings between the 2 versions? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "T. Valko" wrote in message ... calculation times screencap: http://img267.imageshack.us/img267/6...ltimes1rq2.jpg The multiplication version is slightly faster when the number of variables is small. As the number of variables increases the calc time also increases "signifcantly". The --(ISNUMBER(MATCH(...)) version is faster in 2 out of 3 tests. Also note that the number of variables seems to not slow this version down (although the number of variables is small). Calc times measured using Charles Williams RangeTimer method. -- Biff Microsoft Excel MVP "Bernard Liengme" wrote in message ... Thanks for this, We life and learn cheers -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... If you've seen my posts over the years, you might have noticed my *distaste* for the unary form of Sumproduct. Besides the fact that it *can't* be used on different sized ranges (column vs. row), and the fact that it allows text numbers to be by-passed without any notice, it *also* doesn't work in this scenario. Just revise your test formula from unary to asterisk, which is the form my suggested formula used. =SUMPRODUCT((A1:A9={"A","B"})*B1:B9) No problem with this ... is there? -- Regards, RD ------------------------------------------------------------------------ - -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------ - -- "Bernard Liengme" wrote in message ... That will earn a #VALUE! error Let A1:A9 have letters a, b or c in each cell Let B1:B9 have numbers Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9) But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and FALSE values. But since B1:B9 is a nine element array, we have a mismatch. T Valko's method with ISNUMBER(MATCH overcomes the problem and let one use {"A","B"} best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "RagDyer" wrote in message ... Little shorter: =SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"}) *'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70) -- HTH, RD ---------------------------------------------------------------------- - ---- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------- - ---- "torooo" wrote in message ... Thanks for your assistance, the formula works (without the extra parenthesis). Best |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| HELP !!! SUMIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
| SumIf or SumProduct | Excel Worksheet Functions | |||
| SUMIF,SUMPRODUCT | New Users to Excel | |||
| sumif or sumproduct? | Excel Worksheet Functions | |||
Linear Mode
