Using SUMPRODUCT with LEN to check if the amount of characters is okay minus second row ?

[MasterBash]

Hi,

I am currently using this formula :

=IF($B$5-1=SUMPRODUCT(--(LEN((Table1[Column3]))=10)),"OK","Not OK")

So what it does is calculate if $B$5-1 = the sum of the product in table1 column3 that are 10 characters long.

Column3
24/10/2024
1006784525
1006784527
1006784522
9876543210
0123456789
1234567890
5559997775

Problem is that the second row (first row after the table header) is a date, and depending on the date, it may or may not count the date as 10 characters long, so it may or may not be ok.

I am wondering - is there a way to simply ignore the second row (lets call it A2) ? Or is there a better way to do it ?

Thank you.

 

[Peter_SSs]

I assume that the table column values are text values. If that is so, what about this (looks for 10 characters but excludes if a "/" is found)?

=LET(t,Table1[Column3],IF($B$5-1=ROWS(FILTER(t,(LEN(t)=10)*(FIND("/",t&"/")=11))),"OK","Not OK"))

Or, taking your previous approach of just looking for 10 characters, and omitting the first value in the column as you suggested

=LET(t,Table1[Column3],IF($B$5-1=SUMPRODUCT(--(LEN(INDEX(t,2):INDEX(t,ROWS(t)))=10)),"OK","Not OK"))

 

[MasterBash]

Thank you. I am using the second option - Works great.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.