Counting number of times a word appears in a group of cells dynamically!

Evening everyone,

I have an spreadsheet that contains text strings in cell B2:CE322.

The text strings are in the following format, however contain a variety of names and numbers:

Roger+1; Linda+4; Anne+3

I want a function where I can count in the range B2:CE322 the number of times the following appears: Roger+1

In this formular however, I want it to read in Roger from Cell A1 and the number 1 from A2. So if I want to change and count the number of times Linda+2 appears, I would change:

Cell A1: From Roger to Linda
Cell A2: From 1 to 2

Hopefully someone will be able to help!

Many thanks!

Many thanks NBVC, but unfortuantely the formula you suggested does not work

I am really sorry NBVC but I have done exactly what you have said, and I still get an answer of 0!

I am using Excel 2007, does that make a difference?

Hey NBVC,

Thank you so much for all your help!

This is the doc where I cannot get the formula to work!

Cheers

Oops! Managed to get it to work :o)

I have managed to get it to work if Roger+1 is just in the cell.

There are some cells however that have the following in "Roger+1; Linda+4; Anne+3"

If I put Roger in A1 and 1 in A2, I would still like Excel to count this!

Also, if I put Linda in A1 and 4 in A2, again I would like excel to count the cell!

Thanks again for all your help!

Sorry to complicate things even further!

I now have three cells containing:

Linda+1, Roger+1, Gill+4
Gill+4, Linda+1
Roger+1, Linda+1

In A1, I have Roger and in A2 I have 1
In B1, I have Linda, and in B2 I have 1

I now want Excel to count how many cells contain 'Roger+1' and 'Linda+1' together. They can be in either order however.

So on the example above, the answer should be 2!

Last complication! Promise!

If I now change B2 to 0, I don't now want the formulae to count Linda either!

The same is true with A2, if I change that to 0, I don't now want the formulae to count Peter either!

Thanks guys!

So keeping all of the functionality previously mentioned, I now have three cells containing:

Linda+1, Roger+1, Gill+4
Linda+1
Roger+1, Linda+1

In A1, I have Roger and in A2 I have 0
In B1, I have Linda, and in B2 I have 1

I would like a formula, which keeps all mentioned before functionality, however would only count cells which contain 'Linda+1'. i.e As A2 is 0, I don't want to count the word 'Roger' either!