asked on # SUMPRODUCT and MAX LEN for Structured Table

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:**

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)*

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

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.

```
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:

```
=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

Microsoft OfficeMicrosoft ExcelSpreadsheets

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Try this as well

```
=MAX(LEN(Table1[Values]))
```

Confirmed with Ctrl+Shift+Enter
Yeah I couldn't get the last one to work ie. =MAX(LEN(Table1[Values]))

(aside from using CSE)

That's why I went the SumProduct route, thinking that perhaps structured tables didn't support arrays for functions such as =Max(Len(values)).

Clearly they do though, as shown by your solution:

(aside from using CSE)

That's why I went the SumProduct route, thinking that perhaps structured tables didn't support arrays for functions such as =Max(Len(values)).

Clearly they do though, as shown by your solution:

```
=MAX(LEN(Table1[[#Headers],[Values]]))
```

Definitely handy tools these structured tables...I'll have to invest some time to work them out :)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Chris,

have you tried with Ctrl+Shift+Enter?

Its an Array Formula,

Last one will work even you gonna expand your list.

have you tried with Ctrl+Shift+Enter?

Its an Array Formula,

Last one will work even you gonna expand your list.

Ah...I think I see what you mean.

Just tested it, and it seems that if you enter your second formula below, its not dynamic in that it doesn't account for table additions.

Eg. if someone enters and exits that cell and doesn't understand that its an array formula, then the curly braces are gone!

Your formula works perfectly though!

Many thanks again Shums :)

Just tested it, and it seems that if you enter your second formula below, its not dynamic in that it doesn't account for table additions.

```
=MAX(LEN(Table1[[#Headers],[Values]]))
```

In that case, I'd probably stick with your first formula, the SUMPRODUCT approach, because that does seem to account for table additions.```
=SUMPRODUCT(MAX(LEN(Table1[[#Headers],[Values]]))*1)
```

I prefer to avoid array functions, for the reason of which you're no doubt aware, because its easy to blow up the formula.Eg. if someone enters and exits that cell and doesn't understand that its an array formula, then the curly braces are gone!

Your formula works perfectly though!

Many thanks again Shums :)

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

You're Welcome Chris! I am glad I was able to help.

Furthermore, its improved the function without the SumProduct, cheers.

Looks like it was my ignorance of structured table references...I'll have to look further at those.

Many thanks again Shums, much appreciated!