if cell starts with characters formula
 

Hi

I need to count cells in a column starting with certain characters. each
cell's data varies in length. I have tried with @countif( but does not work
if the cell contains other characters after the "prefix".
eg.

row 20 cell 5 apples
row 21 cell 5 apples red
row 22 cell 5 apples green
row 23 cell 5 plums green
row 23 cell 5 plums purple

totals required for apples = 3 (regardless of colour)
total required for plums = 2 (regardless of colour)

@countif(C20:c30,"plums") gives answer of 1 require answer of 2
@countif(C20:c30,"apples") gives answer of 1 require answer of 3

Thanking you in advance

Regards

Norman

--
Message posted via http://www.officekb.com

Hi Norman

use the COUNTIF function
e.g.
=COUNTIF(C20:C30,"*apples*")

Cheers
JulieD


"Norman Kong via OfficeKB.com" wrote in message
...
Hi

I need to count cells in a column starting with certain characters. each
cell's data varies in length. I have tried with @countif( but does not
work
if the cell contains other characters after the "prefix".
eg.

row 20 cell 5 apples
row 21 cell 5 apples red
row 22 cell 5 apples green
row 23 cell 5 plums green
row 23 cell 5 plums purple

totals required for apples = 3 (regardless of colour)
total required for plums = 2 (regardless of colour)

@countif(C20:c30,"plums") gives answer of 1 require answer of 2
@countif(C20:c30,"apples") gives answer of 1 require answer of 3

Thanking you in advance

Regards

Norman

--
Message posted via http://www.officekb.com

It works !!!
Thanks

--
Message posted via http://www.officekb.com

you're welcome - thanks for the feedback

"Norman Kong via OfficeKB.com" wrote in message
...
It works !!!
Thanks

--
Message posted via http://www.officekb.com