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