SUMPRODUCT Critera - If range values start with number

[daffodil11]

Hello all!

I'm trying to count the number of records I have that are 6 characters long and begin with a 6.

I using SUMPRODUCT at the moment, but I'm having issues with the text strings (technically, all values are stored as text).

I've got just under a million rows, so helper column / filter won't work because this locks up Excel.

Please Login or Register  to view this content.

=SUMPRODUCT((LEN(A1:A7)=6)*(LEFT(A1:A7,1)*1=6))

The second half of this is where my logic falls apart. The strictly text rows evaluate to #VALUE and I'm not sure how to circumvent this.

[Jonmo1]

It's because some of the values begin with a Letter
LEFT(A###,1) = A letter
Letter*Number = #VALUE

Here's an outside the box solution, assuming the numbers are whole numbers, no decimal values.

=SUMPRODUCT((A1:A7>=600000)*(A1:A7<=699999))

[BadlySpelledBuoy]

As these values would not match the desired criteria, you could wrap the SUMPRODUCT in IFERROR to get rid of the #VALUE errors.

[canapone]

Hi,

maybe

Please Login or Register  to view this content.

Regards

[Jonmo1]

Think I missed the part about the values being Stored As Text...

Try

=COUNTIF(A1:A7,"6?????")