Trick to use Sumproduct with Text?

Dathan

New Member
Joined
Oct 25, 2006
Messages
49
I'm stuck and could really use some help. :rolleyes:

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! (y)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hello Dathan, I don't think you'd want SUMPRODUCT for this...

try

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

Dathan

New Member
Joined
Oct 25, 2006
Messages
49
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!! (y)

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,151
Members
417,010
Latest member
jnuss03

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?

Disable AdBlock

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
Top