sumif to return an intersection?

[EllenG]

I have the following data sets

worksheet1

TRANCODES 2 3 4 5 6 8 .........99
PARTS
003-0623-00
005-0030-00



worksheet2

PARTS TRANCODE SUM_QTY
003-0018-01 28 10
003-0018-01 40 10
003-0021-00 8 25
003-0021-04 4 63


In worksheet1 I need to compare the part number and the trancode then
return the quantity from worksheet2 -

Suggestions?

 

[Franz Verga]

Nel post *EllenG* ha scritto:

I have the following data sets

worksheet1

TRANCODES 2 3 4 5 6 8 .........99
PARTS
003-0623-00
005-0030-00



worksheet2

PARTS TRANCODE SUM_QTY
003-0018-01 28 10
003-0018-01 40 10
003-0021-00 8 25
003-0021-04 4 63


In worksheet1 I need to compare the part number and the trancode then
return the quantity from worksheet2 -

Suggestions?

Hi Ellen,

Try this formula in B2 of Sheet1 (I suppose you have code parts in column A
from A2 and Trans code in row 1 from B1):

=SUMPRODUCT((Sheet2!$A$2:$A$14=$A2)*(Sheet2!$B$2:$B$14=B$1)*(Sheet2!$C$2:$C$14))

where:

Sheet2!$A$2:$A$14 is the range of Parts on Sheet2,
Sheet2!$B$2:$B$14 is the range of Trans code on Sheet2,
Sheet2!$C$2:$C$14 is the range of Qty on Sheet2.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Mallycat]

Use VLookup. Use the online help for instructions, but basciall it
takes a value from your first sheet, searches in the table in your
second sheet. When it finds a match, it moves to the right the
specified number of columns and returns the value found.

Matt

 

[Franz Verga]

Nel post *Mallycat* ha scritto:

Use VLookup. Use the online help for instructions, but basciall it
takes a value from your first sheet, searches in the table in your
second sheet. When it finds a match, it moves to the right the
specified number of columns and returns the value found.

Matt

This is not the case o VLOOKUP, because on Sheet2 you have 2 references to
look for. This is the case of SUMPRODUCT, as I wrote on my previous post...

 

[Bob Phillips]

IMO it is dangerous to use SUMPRODUCT in this manner, maybe not her but
generally. Far better to match on two values and index into a third column

=INDEX(rng3,MATCH(1,(rng1=val1)*(rng2=val2),0))

as an array foirmula.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Franz Verga]

I prefer to use SUMPRODUCT instead of array formulas, when it's possible.

But why do you think is dangerous to use SUMPRODUCT in this manner?


Nel post *Bob Phillips* ha scritto:

IMO it is dangerous to use SUMPRODUCT in this manner, maybe not her
but generally. Far better to match on two values and index into a
third column

=INDEX(rng3,MATCH(1,(rng1=val1)*(rng2=val2),0))

as an array foirmula.

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Peo Sjoblom]

Bob's formula will work for all type of data in the lookup column but
sumproduct will only work if the data is numeric, thus the index solution
will work in all cases
Don't know if that what Bob meant though.

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Franz Verga]

Nel post *Peo Sjoblom* ha scritto:

Bob's formula will work for all type of data in the lookup column but
sumproduct will only work if the data is numeric, thus the index
solution will work in all cases
Don't know if that what Bob meant though.

Also SUMPRODUCT will work with non numeric data in the lookup columns...

As you can see in the OP example the parts code are not numeric (they look
like numeric, but are strings...)...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Bob Phillips]

Peo highlights one of the problems I was referring to, and there is another.
SUMPRODUCT by its very nature SUMs numbers. So if two matches are found it
will sum them, the lookup will return the first found. Of course it could be
(should be?) argued that both are wrong, or at least the developer should be
aware in the coding, but IMO the adding of two values is the bigger problem.
Murphy's law says that it is bound not to happen in testing, but will in
production when the formula is no longer checked.

BTW, you say you (Franz that is) prefer to use SP rather than array
formulae. SP works on arrays, so is an array formula in that sense, it just
isn't array-entered. I don't know why people are so averse to array-entered
formulae. I think it should we should be using the best tool for the job,
which includes performance (where SP and SUM(IF(... differ little in my
experience, readability (where SP scores over SUM(IF(... IMO).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Franz Verga]

First Problem: summing of numbers. By nature of this kind of problem (sales
by store and sales person, etc.), generally if two or more match are found
someone *want* to sum numbers, not just find the first match. If the case is
that should be just one match, by summing two or more matches you could be
warned that there's some problem with data, that there is some duplication.
So for this kind of reason I still prefer SUMPRODUCT instead of
INDEX(...MATCH(..)).

Second Problem: when I wrote: "I prefer SUMPRODUCT instead of array
formulas", I made a language mistake, because I should have to write: "I
prefer SUMPRODUCT instead of array-entered formulas" and this for two
reasons: first of all I'm lazy, so I prefer press just Enter instead of Ctrl
+ Shift + Enter, and also for performace, for which you can read some very
interesting articles here:

http://www.decisionmodels.com/optspeedj.htm

http://support.microsoft.com/kb/166342/en-us

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Ragdyer]

<<<"I don't know why people are so averse to array-entered formulae.">>>

From my earliest days of becoming aware of these NGs, what sticks in my mind
most was the *constant* critique of a Mr. Harlan Grove, of the waste of XL
resources of unnecessary array formulas and unnecessary function calls.<g>

 

[Bob Phillips]

First Problem: summing of numbers. By nature of this kind of problem (sales
by store and sales person, etc.), generally if two or more match are found
someone *want* to sum numbers, not just find the first match. If the case is
that should be just one match, by summing two or more matches you could be
warned that there's some problem with data, that there is some duplication.
So for this kind of reason I still prefer SUMPRODUCT instead of
INDEX(...MATCH(..)).

Only if you are doing a sum, but in this instance the guy wanted an
intersect, which by definition is a lookup. Hence my comment that SP is
dangerous.

Second Problem: when I wrote: "I prefer SUMPRODUCT instead of array
formulas", I made a language mistake, because I should have to write: "I
prefer SUMPRODUCT instead of array-entered formulas" and this for two
reasons: first of all I'm lazy, so I prefer press just Enter instead of Ctrl
+ Shift + Enter, and also for performace, for which you can read some very
interesting articles here:

I would argue strongly that SP is no better performance wise than an
arrauy-entered SUM(IF. No tests that I have ever performed would suggest
otherwise.

http://www.decisionmodels.com/optspeedj.htm

http://support.microsoft.com/kb/166342/en-us

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I have read the first two, and the articles referred to therein, which as
far as I can see support my arguments. As for the third, I will pass on that
one for now.

 

[Peo Sjoblom]

I was not talking about the criteria ranges but what will be the result of
the formula, you will never get sumproduct to return a text value, I am not
talking about the 2 criteria ranges in your formula, I am talking about the
quantity range which obviously will work in this case since it is a number,
however if this range Sheet2!$C$2:$C$14 held text values your formula will
return a value error, it even will return a value error if there is a single
occurrence of a text value like a "" from a formula. Using index with match
can only return a #N/A error and that would be if there is no match

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Biff]

I think it's a good "habit" to get into, being as efficient as possible, but
I've also made comments such that, at some point it becomes overkill.

Most users don't have huge files where efficiency is a primary concern.

My "favorite" pet peeve is the use of 9.99999999999999E307. I understand the
reasoning but using that same reasoning why don't you ever see:

REPT("z",32767)

Would that be overkill?

Biff

 

[Franz Verga]

Ok, maybe I misunderstood...

Now I agree.

When you have multiple conditions, so you can't use VLOOKUP, COUNTIF or
SUMIF, and if the expected return value is text, you cannot absolutely use
SUMPRODUCT but you must use INDEX + MATCH array-entered.

To avoid error value you could write SUMPRODOCUT in this way:

=SUMPRODUCT((rng1=cond1)*(rng2=cond2),(rng3))

In this way if rng3 should have a single occurrence of a text value like a
"" there would be no error and if rng3 should be a text range the result
value would be 0.

Nel post *Peo Sjoblom* ha scritto:

I was not talking about the criteria ranges but what will be the
result of the formula, you will never get sumproduct to return a
text value, I am not talking about the 2 criteria ranges in your
formula, I am talking about the quantity range which obviously will
work in this case since it is a number, however if this range
Sheet2!$C$2:$C$14 held text values your formula will return a value
error, it even will return a value error if there is a single
occurrence of a text value like a "" from a formula. Using index with
match can only return a #N/A error and that would be if there is no
match

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Bob Phillips]

Although Harlan has frequently railed against what he calls unnecessary
function calls, I agree with you Biff that this is often overkill,
especially if it leads to more obscure formulae. I also sigh when I see
..99999999999999E307. But having said that, I don't think that he (Harlan)
has ever made that argument for, or against, array entered formulae.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

If you want to lead to confusion you could ...

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Franz Verga]

Eh? I don't understand what you mean...

English is not my mother language, so sometimes a misunderstanding can
happen...


Nel post *Bob Phillips* ha scritto:

If you want to lead to confusion you could ...

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy