SUMIF, SUMPROD, LEN, LEFT, cond sum checking string's length and initial character cells

Kind all,

I'm getting crazy with this issue any help will be appreciated!

I've two columns: on M text descriptions, on I values. I need to SUM values only if it find a 16 characters number (ex. 4001098776554332) but must starting with "4" as first character in M range.

Some unlucky test: SUMPRODUCT((LEN('SQL Results'.M2:M65536=16))*(LEFT('SQL Results'.M2:M65536,1)="4"),('SQL Results'.I2:I65536) ...and many other unsuccessful variants.
I also tried with SUMIF('SQL Results'.M2:M65536;"^4...............";'SQL Results'.I2:I65536) using regular expression in OpenOffice. Seems function, but I prefer to do it with SUMPROD. This can sounds good in your experience?

I try to find also the number (not the SUM) using SUMPRODUCT(--(LEN('SQL Results'.M2:M65536)=16))...but it's not correct, because it count also letters and space (this formula return the count of 16 characters cells only, but i need 16 numbers starting with 4 as very first cell character.

Thank you for your kind collaboration.

In Excel you need to separate the sheet name from the cell references with !, so your first formula would be:

=SUMPRODUCT((LEN('SQL Results'!M2:M65536)=16)*(LEFT('SQL Results'!M2:M65536)="4"),('SQL Results'!I2:I65536))

and if you wanted to omit cells with spaces:

=SUMPRODUCT((LEN('SQL Results'!M2:M65536)=16)*(LEFT('SQL Results'!M2:M65536)="4")*(ISNA(SEARCH(" ",M2:M65536))),('SQL Results'!I2:I65536))

If you want to count them, then do this:

=SUMPRODUCT((LEN('SQL Results'!M2:M65536)=16)*(LEFT('SQL Results'!M2:M65536)="4")*(ISNA(SEARCH(" ",M2:M65536))))

Do you really need to have such long ranges?

Hope this helps.

Pete

Thank you for your quick replay Pete, I know the ! quotation in Excel, but in OpenOffice is different. Anyway with ! or . I still receive the same "bracket error".

And yes, unfortunately data fill the entire range

Check where I've put my brackets - you had them in the wrong place for the LEN expression.

Pete

A little step haed! Thank you, but still have error bracket with this formula...SUMPRODUCT((LEN('SQL Results'.M2:M65536)=16)*(LEFT('SQL Results'.M2:M65536)="4"),('SQL Results'.I2:I65536))

Any idea?

Marcol, you are a legend! Thank you!

The problem was (as you highlighted) ";" instead of "," not brackets! And it's return the same value of =SUMIF($'SQL Results'.M2:M65536;"^4...............";$'SQL Results'.I2:I65536)

Excel-lent best forum ever!

Just remember, though, that this is a forum for Excel, not Open Office Calc or Google Docs ...

Pete