counting characters issue

[Guest]

I have the following data. i need to count those recorrds where there length
is less than 12 characters.

I used =SUMPRODUCT(--(LEN(Sheet1!F:F)<12)) but excel falls down saying the
cells are formatted as text or contain an apostrophe. i tried to format the
cells as general but still won't work. Other users will use this so i don't
want a lot of user intervention.

FunctLocation
12087359305PV1525
12087325530DRIVE
12087350101
12087320109PV4201
12087325350PIPING
12087325001
12087325759LR-F01

 

[Roger Govier]

I think your problem is selecting the whole of column F
Sumproduct cannot take whole column, only a defined range.
=SUMPRODUCT(--(LEN(F1:F7)<12)) rturned an answer of 2 with your data

 

[Guest]

Hi,

I suggest trying text to columns on the column F:F.

1) Select column F:F
2) Data -> Text to Columns (on the menu bar)
3) Delimited
4) Choose ' as the delimiter
5) Finish

Does that work?

Regards,
A

 

[Bob Phillips]

This worked for me

=SUMPRODUCT(--(LEN(Sheet1!F1:F1000)<12))

it counts empty cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

No unfortunately A. It appears that Roger (another answer) has identified the
same conclusion I had come too via another thread I have. I need to keep it
really simple. It looks like the way to do it is to make sure that may ranges
are sufficiently large to cater for any data set that I or a user paste in.

 

[olasa]

This works for me
=SUMPRODUCT(--(LEN(Sheet1!F1:F65536)<12))

HTH
Ola Sandströ