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
0
forum (466)
3/24/2005 9:25:17 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
332 Views

Similar Articles

[PageSpeed] 13

Hi Norman

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

Cheers
JulieD


"Norman Kong via OfficeKB.com" <forum@OfficeKB.com> wrote in message 
news:72bab8bbfa3a475e8d4b7a72116d0789@OfficeKB.com...
> 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 


0
JulieD1 (2295)
3/24/2005 9:31:32 AM
It works !!!
Thanks

-- 
Message posted via http://www.officekb.com
0
forum (466)
3/24/2005 9:49:52 AM
you're welcome - thanks for the feedback

"Norman Kong via OfficeKB.com" <forum@OfficeKB.com> wrote in message 
news:8e1689c9476249afb84caa61e8a7fe6f@OfficeKB.com...
> It works !!!
> Thanks
>
> -- 
> Message posted via http://www.officekb.com 


0
JulieD1 (2295)
3/24/2005 10:18:24 AM
Reply:

Similar Artilces: