Excel 2007 : counting the number of times an identifier being called in a column

[superkid]

Hi
I have a cell which is suppose to count the number of times the weighted score is being called in all the cells in the column.

For example
Col A Cell 1
i am a boy and i like fishing.

Col A cell 2
The boy is running. Oh boy, he's fast.

Col A cell 3
The boy is the girls brother.

Suppose the identifier for Col A is boy.

The result should display 4

Any idea on how to do this?

Thank you

[shg]

it could be done, with some complexity, with formulas, and more easily with VBA. What's your level of familiarity?

[NBVC]

tRY:

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"boy","")))/LEN("boy")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

You can place "boy" in another cell and reference it instead, if desired.

[superkid]

wow i learnt something new what is there a difference between ctrl shift enter and enter?

thanks

[NBVC]

CTRL+SHIFT+ENTER is used with Array Formulas...

See here for a good tutorial on these types of formulas

[superkid]

one small problem

For example
Col A Cell 1
i am a [+3, boy] and i like fishing.

Col A cell 2
The [+2,boy] is running. Oh [+3, boy], he's [-1,fast].

Col A cell 3
The [+1, boy] is the girls brother.

Suppose the identifier for Col A is still boy and now I would like to count the number of times +3 is being called for the identifier. Any solution for this.

Answer should be now be 2

thanks

[NBVC]

Are you putting the identifiers in other cells? If so, how are you entering them?

[NBVC]

Since I will be going to bed soon... I thought I would attempt a guess...

I will assume you have "+3" in cell D1 and "boy" in cell E1 (both without quotes and entered as text strings).

Then, assuming the format [+3, boy] is consistent...

Try:

Please Login or Register  to view this content.

Again confirmed with CTRL+SHIFT+ENTER.

[superkid]

thank you so much look like it works :D