Share via

Excel Using LEN in Sumifs

Anonymous
2016-05-13T18:13:39+00:00

Hi

I'm trying to use the LEN function with the Sumifs and it doesn't work

I'm using Office 2007

my formula is

SUMIFS(Data!$H:$H,Data!$K:$K,B12,Data!$D:$D,$B$8,Data!$E:$E,LEN(Data!$E:$E)>=20)

The last one: Data!$E:$E,LEN(Data!$E:$E)>=20

I need to find in col E all cells with a len equal or over 20

The results is the total of everything. Looks like he doesn't care about a minimum of 20 caracteres

Thanks

Have a nice weekend

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-05-13T18:53:35+00:00

O_Mario wrote:

I'm trying to use the LEN function with the Sumifs and it doesn't work

[....]

SUMIFS(Data!$H:$H,Data!$K:$K,B12,Data!$D:$D,$B$8,Data!$E:$E,LEN(Data!$E:$E)>=20)

We cannot.  Use the following instead:

=SUMPRODUCT(Data!$H1:$H10000,

(Data!$K1:$K10000=B12)*(Data!$D1:$D10000=$B$8)*(LEN(Data!$E1:$E10000)>=20))

Note that I changed ranges like H:H to H1:H10000.  H:H is very bad because it causes Excel to process 1+ million rows of data; and for SUMPRODUCT, to create temporary arrays of 1+ million data.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-05-13T19:23:25+00:00

    Hi Jo

    Thanks for the answer. I will change my formula for the sumproduct

    And thanks also about your suggestion to use a max range. I will add it to my codes

    Bye

    Was this answer helpful?

    0 comments