Help on a function to count

[overworked]

Can someone tell me if there is a function that will lookup all similiar text within a cell even if the cell contains other data within the cell.

example.

Lets say, Column A contains the names, ages and location of over 200 people, and I wanted to count how many people in the column are 35 years of age.

Data all looks like this.

Jeff Jones, 35, San Diego, CA

Can this be done?

[NBVC]

Try Countif()

e.g. =COUNTIF(A1:A9,"*"&35&"*")

adjust range to suit.

You can replace 35 with a cell reference containing the age too.

[overworked]

Can someone tell me if there is a function that will lookup all similiar text within a cell even if the cell contains other data within the cell.

example.

Lets say, Column A contains the names, ages and location of over 200 people, and I wanted to count how many people in the column are 35 years of age.

Data all looks like this.

Jeff Jones, 35, San Diego, CA

Can this be done?

Try Countif()

e.g. =COUNTIF(A1:A9,"*"&35&"*")

adjust range to suit.

You can replace 35 with a cell reference containing the age too.

Thank you.

Does the formula change if you want to count how many people are from CA? compared to NV or OR.

example Jeff Jones, 35, San Diego, CA

I put in =Countif(A2:I26;"*"&CA&"*") but it is not returning anything?

any input would be appreciated.

[martindwilson]

=COUNTIF(A1:A26,"*ca*")
but that would also count cathy!!

[DonkeyOte]

As Martin says you need to reduce possibility of errors, if State is preceded with a comma and is always at the end of the string then perhaps:

=COUNTIF(A1:A26,"*, CA")