Return text using Sumproduct

  • Thread starter Thread starter deeds
  • Start date Start date
D

deeds

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
 
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))
 
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
Click to expand...

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
 
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
 
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
 
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!
 
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
 
deeds said:
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!
Click to expand...

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.
 
Great Work folks! Got it to work with all examples....now I just choose one
and go with it! Thanks again!
 
Back
Top