Trick to use Sumproduct with Text?

[Dathan]

I'm stuck and could really use some help.

I've just discovered the power of the SumProduct function and I'm trying to use it to analyze a range A2:A10. One and only one cell in A2:A10 will contain text, and I'm trying to get SumProduct to return the text.

I thought it would be simple till I read the Excel help file. It says that SumProduct treats array entries that are not numeric as if they were zeros.

Is there a trick to fool Excel into doing what I need? Thanks!

 

[barry houdini]

Hello Dathan, I don't think you'd want SUMPRODUCT for this...

try

=LOOKUP(REPT("z",255),A2:A10)

 

[Dathan]

If your only tool is a hammer, all your problems look like nails, eh? I guess I am over-zealous with the sumproduct function.

Barry, your solution worked perfect. thank you!!

I don't understand why it works though, can you (or anyone if Barry's not online) step me through what it does?

 

[barry houdini]

The formula actually returns the last text value in the range. As you only have one text value in A2:A10 then the last one will always be the only one.

If you have text in A2 and text in A7 and a number in A8 it will return the text in A7

The lookup value is REPT("z",255) which is 255 "z"s - this is the "largest" possible text value in excel.

With LOOKUP the formula should return the greatest value less than the lookup value (of the same data type, text or number) in the lookup range (A2:A10). Because no text value can be greater than REPT("z",255) the formula should return the "largest" text value in the range.....but there's another twist.....

...when using LOOKUP the lookup range should be sorted ascending, so the largest value should always be the last one. The lookup range may not actuallt be sorted, but because excel assumes it will be it returns the last text value in the range.

Note: Similarly you can extract the last numeric value with:

=LOOKUP(9.99999999999999E+307,A2:A10)