Return text using Sumproduct
 

Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

Thanks

Return text using Sumproduct
 

Try this index/match alternative which works for both text & numbers,
array-entered (press CTRL+SHIFT+ENTER):
=INDEX(E5:E9,MATCH(1,(C5:C9=A1)*(D5:D9=A2),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"deeds" wrote:
Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

Thanks

Return text using Sumproduct
 

On Tue, 10 Jun 2008 08:21:01 -0700, deeds
wrote:

Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

Thanks

SUMPRODUCT will not return TEXT. It is a function that does multiplication and
addition and trying to do arithmetic operations on text will usually return a
#VALUE! error.

Something like:

=INDEX(E5:E9,MATCH(1,(C5:C9=A1)*(D5:D9=A2),0))

entered as an **array** formula (i.e. entered by holding down <ctrl<shift
while hitting <enter ) should do what you want.
--ron

Return text using Sumproduct
 

The two words making up the function name, SUM and PRODUCT, should be a clue
that it can only returns numerical values, actually, only a single value per
SUMPRODUCT function call; however, you can use that returned value in other
formulas to do things. It is not entirely clear from your example what you
want returned... a single cell's text (only one row will ever meet the
tested for condition) or, multiple cell text (more than one row will meet
the tested for condition) perhaps concatenated together. For the first, you
can use SUMPRODUCT as an argument to an INDEX function call, such as like
this...

=INDEX(E1:E9,SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*ROW( E5:E9)))

Note the range in the first argument starts at Row 1 (because the SUMPRODUCT
is returning a row number, so the offset to be applied to the INDEX function
has to start at the beginning of the column). I am not sure how you would do
what I think the second condition would require.

Rick



"deeds" wrote in message
...
Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

Thanks

Return text using Sumproduct
 

I guess I should point out that my INDEX formula was for example purposes
only... Max and Ron showed you the better way to pull a single value from a
range using the information you indicated you had.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
The two words making up the function name, SUM and PRODUCT, should be a
clue that it can only returns numerical values, actually, only a single
value per SUMPRODUCT function call; however, you can use that returned
value in other formulas to do things. It is not entirely clear from your
example what you want returned... a single cell's text (only one row will
ever meet the tested for condition) or, multiple cell text (more than one
row will meet the tested for condition) perhaps concatenated together. For
the first, you can use SUMPRODUCT as an argument to an INDEX function
call, such as like this...

=INDEX(E1:E9,SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*ROW( E5:E9)))

Note the range in the first argument starts at Row 1 (because the
SUMPRODUCT is returning a row number, so the offset to be applied to the
INDEX function has to start at the beginning of the column). I am not sure
how you would do what I think the second condition would require.

Rick



"deeds" wrote in message
...
Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

Thanks

Return text using Sumproduct
 

Thanks much! Sorry to expand this, (I thought I would be able to get it by
your example)..anyways, here is exactly what I am trying to do:

Columns A, B, C, D I want to search those 4 columns, if it finds a specific
product number in ANY of those columns, bring back the corresponding text in
column E. The product number will only show up in one of those columns. Any
ideas?

Thanks again!

"Max" wrote:

Try this index/match alternative which works for both text & numbers,
array-entered (press CTRL+SHIFT+ENTER):
=INDEX(E5:E9,MATCH(1,(C5:C9=A1)*(D5:D9=A2),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"deeds" wrote:
Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

Thanks

Return text using Sumproduct
 

If the earlier did answer your original query,
pl take a moment to press the "Yes" button below

As for your new query, my thoughts would be to try something along these
lines, indicatively:

=
IF(ISNA(MATCH(1)),IF(MATCH(2)),
IF(ISNA(MATCH(3)),IF(ISNA(MATCH(4)),
INDEX(ColE,MATCH(4)),INDEX(ColE,MATCH(3)),
INDEX(ColE,MATCH(2)),INDEX(ColE,MATCH(1)))

where 1,2,3,4 would contain the sequential checks on the 4 cols
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"deeds" wrote:
Thanks much! Sorry to expand this, (I thought I would be able to get it by
your example)..anyways, here is exactly what I am trying to do:

Columns A, B, C, D I want to search those 4 columns, if it finds a specific
product number in ANY of those columns, bring back the corresponding text in
column E. The product number will only show up in one of those columns. Any
ideas?

Thanks again!

Return text using Sumproduct
 

Typo in this line, corrected:
IF(ISNA(MATCH(1)),IF(ISNA(MATCH(2)),
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Return text using Sumproduct
 

deeds wrote...
Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

You don't, as others have pointed out. FTHOI, another alternative,

=LOOKUP(2,1/(C5:C9=A1)/(D5:D9=A2),E5:E9)

This formula returns the LAST match. If there's only one match in cols
C and D, then LAST is also FIRST, so this would return the same result
as the INDEX(..,MATCH(..)) formulas. When there's more than one match,
the INDEX(..,MATCH(..)) formulas return the first match.

If you want to choose which of possibly many matches to return, you'd
need to use something like the following array formula.

=INDEX(E5:E9,SMALL(IF((C5:C9=A1)*(D5:D9=A2),ROW(E5 :E9)-4), N ))

where N indicates the Nth match from the top.

Return text using Sumproduct
 

Great Work folks! Got it to work with all examples....now I just choose one
and go with it! Thanks again!

"Harlan Grove" wrote:

deeds wrote...
Looking to return text using sumproduct:

=SUMPRODUCT((C5:C9=A1)*(D5:D9=A2)*(E5:E9))

where E5:E9 contains text which I want to return.

How do I use sumproduct to return text? It keeps giving me #Value!

You don't, as others have pointed out. FTHOI, another alternative,

=LOOKUP(2,1/(C5:C9=A1)/(D5:D9=A2),E5:E9)

This formula returns the LAST match. If there's only one match in cols
C and D, then LAST is also FIRST, so this would return the same result
as the INDEX(..,MATCH(..)) formulas. When there's more than one match,
the INDEX(..,MATCH(..)) formulas return the first match.

If you want to choose which of possibly many matches to return, you'd
need to use something like the following array formula.

=INDEX(E5:E9,SMALL(IF((C5:C9=A1)*(D5:D9=A2),ROW(E5 :E9)-4), N ))

where N indicates the Nth match from the top.