troubleshooting Question

SUMPRODUCT and MAX LEN for Structured Table

Avatar of Chris
ChrisFlag for Australia asked on
Microsoft OfficeMicrosoft ExcelSpreadsheets
8 Comments1 Solution180 ViewsLast Modified:
Hi Excel function experts,

I've a problem with a non-array SumProduct formula that isn't working with a Structured Table.
My system: Excel 2013, 32-bit (on Win7)

I'd like to get the max length of a column of text values in Table1, which is as below.
The answer I'm looking for is 6 (ie. the length of the last word, "longer").

The answer I get seems to depend on the cell location of my formula.
If its on the same row as dog, I get 3.
Next row I get 2
Last row I get 6, which is the correct answer.
In other words, it seems as though my column reference is just returning the first row, and is not an absolute column reference.

Table1:
Values
---------
dog
hi
longer                            
My formula is as follows:
=SUMPRODUCT(MAX(LEN(Table1[Values]))*1)
It evaluates fine via F9:
=SUMPRODUCT(MAX({3;2;6})*1)
=SUMPRODUCT(6*1)
=6
And it evaluates fine if array-entered (ie. Ctrl Shift Enter)
But I've never had to do that with Sumproduct during the past 10 years.

Having not kept pace with Excel's changes for a few years, it might be to do with my ignorance of structured table references.
But 'maybe' its to do with SumProduct and structured tables.
If the latter, I'm then wondering if anyone has any suggestions, or a neat non-array alternative.

For example, I can get it to work with this:
(where 9.99+E100 is a suitably large number, and the match with type 1 returns the next largest number)
=INDEX(LEN(Table1[Values]),MATCH(9.99+E100,INDEX(LEN(Table1[Values]),0,1),1))
That formula suggests the column reference is working fine.
But it's an ugly formula for someone else to have to work out (or me to figure out in a year's time).
I'd much prefer a simple SUMPRODUCT (or something similar snappy, and non-array entered).
Or maybe even a total row at the bottom of the structured table that returns the Max Length.

Any suggestions?

Many thanks in advance,
Chris
ASKER CERTIFIED SOLUTION
Shums
Excel VBA Developer
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros