Counting Text Values ?

[blewyn]

How do I use worksheet functions to count the number of fields in a
database that contain a specific text string, or include specific
characters within a text string ?

For example :

Car Price
Landcruiser 40
Landrover 30
Lada 10
Rolls 90
Lada 10
Landrover 30

I would like a cell to count the number of occurrences of 'Lada' in
this table, and the answer should be 2. I tried using DCOUNT but it
only works on numbers. I would also (ideally) like to be able to
count all the cars that start with 'La', so the answer should be 5.
Is this possible without writing a macro ? ie, can it be done with
worksheet formulae ?

Thanks,

Blewyn

 

[Ron de Bruin]

You can use a wildcard in CountIf

=COUNTIF(A1:A10,"Lada")
=COUNTIF(A1:A10,"La*")
=COUNTIF(A1:A10,"*Lada*")

 

[blewyn]

Well apart from a win on the lottery, 6 months in a gym and a radical
diet change, that's EXACTLY what I needed. Thanks !

One more question - how do I use multiple criteria, for example, if I
use the same table but add a 'colour' column, how do I count the
instances of brown Ladas vs green ones ?

Thanks,

Blewyn

PS Are you in any way related to Mrs de Bruin who taught at Dyffryn
Ogwen School in North Wales ? (it's an uncommon name in Wales but I've
no idea how common it is in Holland) Cheers.

 

[Ron de Bruin]

Well apart from a win on the lottery, 6 months in a gym and a radical

diet change, that's EXACTLY what I needed. Thanks !

how do I use multiple criteria

Go to this page and post back if you need help
http://www.j-walk.com/ss/excel/tips/tip52.htm

PS Are you in any way related to Mrs de Bruin who taught at Dyffryn
Ogwen School in North Wales ? (it's an uncommon name in Wales but I've
no idea how common it is in Holland) Cheers

If she was nice? YES
No I don't know here.

De Bruin is a common name in Holland

 

[Ron de Bruin]

I forget one
http://www.j-walk.com/ss/excel/tips/tip74.htm