count cells with text only

[Svein Erik]

How is the formula to count text only from some cells?

Sven

 

[Bob Phillips]

Sven,

Do you mean the number of cells containing text against numbers?

Try this

=COUNTA(A1:A10)-COUNT(A1:A10)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Sven!

One way:

=SUMPRODUCT(--(ISTEXT($A$1:$A$100)=TRUE))



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob Phillips]

Hi Norman,

Hate to admit that your solution is better than mine, so I decided to pick
holes in it instead<vbg>

You don't need =TRUE

=SUMPRODUCT(--(ISTEXT($A$1:$A$100)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Bob!

Doh!!

To Sven:

The reason why Bob is right is that on it's own ISTEXT(A1) will return
TRUE or FALSE so the =TRUE is superfluous.

Incidentally, we must use that -- before the expression because we
need to coerce the TRUE and FALSE returns to 1 and 0.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Gord Dibben]

Svein

One method. Count all cells with data and subtract cells with numerics.

=COUNTA(A1:A24)-COUNT(A1:A24)

Gord Dibben Excel MVP