# Trick to use Sumproduct with Text?

#### Dathan

##### New Member
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!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hello Dathan, I don't think you'd want SUMPRODUCT for this...

try

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

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?

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)

Replies
1
Views
626
Replies
15
Views
775
Replies
1
Views
85
Replies
2
Views
110
Replies
18
Views
968

1,218,560
Messages
6,143,200
Members
450,469
Latest member
brent3162

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back