COUNTIF: seems easy but....

Laurent · Mar 21, 2007

Hello,

how can I use the countif function to count the number of star (*) in a
range ?

If I do =COUNTIF(B1:B8;"=*"), I have the number of cells because * is
understood as a wild card...

Thanks in advance

Laurent

KL · Mar 21, 2007

if you want the number of cells that contain * then:

=COUNTIF(B1:B8,"=*~**")

if you want the number of * in all cells then:

=SUMPRODUCT(LEN(B1:B8)-LEN(SUBSTITUTE(B1:B8,"*","")))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36