Replace SUMIF with SUMPRODUCT

[torooo]

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.

 

[Bernard Liengme]

=SUMPRODUCT((('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))
UNTESTED
best wishes

 

[Luke M]

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)

 

[torooo]

Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[T. Valko]

Another way:

=SUMPRODUCT(--(ISNUMBER(MATCH('M:\WORKBOOK1\SHEET1'!$A$7:$A$70,{"INT","IT","MA"),0))),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70)

 

[T. Valko]

Just remember to enter it as an array. (Ctrl+Shift+Enter)

No need to array enter.

 

[RagDyer]

Little shorter:

=SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"})
*'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70)

 

[Bernard Liengme]

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

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 !
---------------------------------------------------------------------------

Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[RagDyer]

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 !
---------------------------------------------------------------------------

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

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 !
---------------------------------------------------------------------------

Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[Bernard Liengme]

Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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 !
---------------------------------------------------------------------------

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

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
!
---------------------------------------------------------------------------
Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[T. Valko]

calculation times screencap:

http://img267.imageshack.us/img267/6321/cacltimes1rq2.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

Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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 !
---------------------------------------------------------------------------

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

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
!
---------------------------------------------------------------------------
Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[Ragdyer]

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 !
---------------------------------------------------------------------------

Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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

!

--------------------------------------------------------------------------

-

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

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
!
------------------------------------------------------------------------ ---
Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[Ragdyer]

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 !
---------------------------------------------------------------------------

calculation times screencap:

http://img267.imageshack.us/img267/6321/cacltimes1rq2.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

Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

------------------------------------------------------------------------- --

!

------------------------------------------------------------------------- --

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

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
!
----------------------------------------------------------------------- ----
Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[T. Valko]

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

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 !
---------------------------------------------------------------------------

calculation times screencap:

http://img267.imageshack.us/img267/6321/cacltimes1rq2.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

Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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?

------------------------------------------------------------------------- --
Please keep all correspondence within the NewsGroup, so all may
benefit

!

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

Little shorter:

=SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"})
*'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70)

----------------------------------------------------------------------- ----
Please keep all correspondence within the NewsGroup, so all may benefit
!

----------------------------------------------------------------------- ----
Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

 

[Ragdyer]

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 !
---------------------------------------------------------------------------

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

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

!

--------------------------------------------------------------------------

-

calculation times screencap:

http://img267.imageshack.us/img267/6321/cacltimes1rq2.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


Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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 !

------------------------------------------------------------------------

-
--

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

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
!

----------------------------------------------------------------------

-
---- news:8cff1f73-51ee-4c60-9609-2370f367c1c3@s12g2000prg.googlegroups.com...

Thanks for your assistance, the formula works (without the extra
parenthesis).

Best