Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

this will count the filled out cells, subtract the count with numbers and
divide by the total count to give you the % filled with text and not numbers

=(COUNTA(A1:J1)-COUNT(A1:J1))/(COUNTA(A1:J1)+COUNTBLANK((A1:J1)))
--
If this helps, please remember to click yes.


"coltheplumb" wrote:

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

This will count only those cells that contain TEXT (that includes formula
blanks ""):

=COUNTIF(A1:J1,"*")

To exclude formula blanks (if present):

=COUNTIF(A1:J1,"?*")

--
Biff
Microsoft Excel MVP


"coltheplumb" wrote in message
...
Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled
it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a
letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

On Wed, 9 Sep 2009 13:46:13 -0700, coltheplumb
wrote:

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

Try this formula:

=SUMPRODUCT(1-ISNUMBER(0+A2:J2))/COLUMNS(A2:J2)

Hope this helps / Lars-Åke

Ok thanks for All the replies 3 different ways ;-) i shall try those tomorrow
n let you all now how it all went. Cheers xx

"coltheplumb" wrote:

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

OK well first of all cheers u guys
Paul C tried your way but it still counted all cells whether they were
letters/text or numbers.
T.Valko tried your way and it did as you said but i would have had to do a
massive string to cover all the alphabet as i couldnt find a way of doing it
in one lump ie a:z
but the winner is .................
Lars-Ake Aspelin what can i say it does exactly what i needed thanks very
much
Colin XX

"coltheplumb" wrote:

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

T.Valko tried your way and it did as you said but i
would have had to do a massive string to cover all
the alphabet as i couldnt find a way of doing it in
one lump ie a:z

Hmmm...

I don't understand.

You said you want to find the % of cells that contain text. I suggested a
formula that would count only the text entires. I figured you would be able
to just "plug" that into your current formula and get the result you expect.

If this is your data in A1:J1 -

a,b,c,d,1,0,<empty,x,y,z

Then:

=COUNTIF(A1:J1,"*")/COLUMNS(A1:J1)

Returns 0.7 formatted as Percentage = 70%

70% of the cells in the range contain text

Isn't that what you wanted?

--
Biff
Microsoft Excel MVP


"coltheplumb" wrote in message
...
OK well first of all cheers u guys
Paul C tried your way but it still counted all cells whether they were
letters/text or numbers.
T.Valko tried your way and it did as you said but i would have had to do a
massive string to cover all the alphabet as i couldnt find a way of doing
it
in one lump ie a:z
but the winner is .................
Lars-Ake Aspelin what can i say it does exactly what i needed thanks very
much
Colin XX

"coltheplumb" wrote:

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells
have
been filled over the range ie, if 5 out of the ten cells have been filled
it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a
letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all
help
much appriciated
Thanks Colin xx

Hi T.Valco
i copied your formular accross but it still counts text and numbers what i
was after was just to count text and not numbers ie,

A B C D E etc
w v t 2 g 80% if it just this 5 cell range( the number 2
isnt counted)

but thanks for your input cheers colin x
"T. Valko" wrote:

T.Valko tried your way and it did as you said but i
would have had to do a massive string to cover all
the alphabet as i couldnt find a way of doing it in
one lump ie a:z

Hmmm...

I don't understand.

You said you want to find the % of cells that contain text. I suggested a
formula that would count only the text entires. I figured you would be able
to just "plug" that into your current formula and get the result you expect.

If this is your data in A1:J1 -

a,b,c,d,1,0,<empty,x,y,z

Then:

=COUNTIF(A1:J1,"*")/COLUMNS(A1:J1)

Returns 0.7 formatted as Percentage = 70%

70% of the cells in the range contain text

Isn't that what you wanted?

--
Biff
Microsoft Excel MVP


"coltheplumb" wrote in message
...
OK well first of all cheers u guys
Paul C tried your way but it still counted all cells whether they were
letters/text or numbers.
T.Valko tried your way and it did as you said but i would have had to do a
massive string to cover all the alphabet as i couldnt find a way of doing
it
in one lump ie a:z
but the winner is .................
Lars-Ake Aspelin what can i say it does exactly what i needed thanks very
much
Colin XX

"coltheplumb" wrote:

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells
have
been filled over the range ie, if 5 out of the ten cells have been filled
it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a
letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all
help
much appriciated
Thanks Colin xx

i copied your formular accross but it still counts text and numbers
A B C D E
w v t 2 g
80% if it just this 5 cell range( the number 2 isnt counted)

If the formula is counting the 2 in that sample then that 2 is a TEXT entry.
The formula I suggested *will not count numeric numbers*. Numbers can be
either data type, TEXT or NUMERIC. Text numbers aren't the same as numeric
numbers.

So, either the cell that holds the 2 is formatted as TEXT or there may be
unseen whitespace characters in the cell that make it TEXT.

--
Biff
Microsoft Excel MVP


"coltheplumb" wrote in message
...
Hi T.Valco
i copied your formular accross but it still counts text and numbers what i
was after was just to count text and not numbers ie,

A B C D E etc
w v t 2 g 80% if it just this 5 cell range( the number 2
isnt counted)

but thanks for your input cheers colin x
"T. Valko" wrote:

T.Valko tried your way and it did as you said but i
would have had to do a massive string to cover all
the alphabet as i couldnt find a way of doing it in
one lump ie a:z

Hmmm...

I don't understand.

You said you want to find the % of cells that contain text. I suggested a
formula that would count only the text entires. I figured you would be
able
to just "plug" that into your current formula and get the result you
expect.

If this is your data in A1:J1 -

a,b,c,d,1,0,<empty,x,y,z

Then:

=COUNTIF(A1:J1,"*")/COLUMNS(A1:J1)

Returns 0.7 formatted as Percentage = 70%

70% of the cells in the range contain text

Isn't that what you wanted?

--
Biff
Microsoft Excel MVP


"coltheplumb" wrote in message
...
OK well first of all cheers u guys
Paul C tried your way but it still counted all cells whether they were
letters/text or numbers.
T.Valko tried your way and it did as you said but i would have had to
do a
massive string to cover all the alphabet as i couldnt find a way of
doing
it
in one lump ie a:z
but the winner is .................
Lars-Ake Aspelin what can i say it does exactly what i needed thanks
very
much
Colin XX

"coltheplumb" wrote:

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells
have
been filled over the range ie, if 5 out of the ten cells have been
filled
it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a
letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all
help
much appriciated
Thanks Colin xx