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

--
Regards vipa

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

--
Regards
Roger Govier
"vipa2000" wrote in message
...
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

--
Regards vipa

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

"vipa2000" wrote:

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

--
Regards vipa

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)


"vipa2000" wrote in message
...
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

--
Regards vipa

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.
--
Regards vipa


"aristotle" wrote:

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

"vipa2000" wrote:

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

--
Regards vipa

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

HTH
Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390872