Changing your scenario slightly, let's say that you enter the word(s) to
count in Columns C and D, and we display the totals in Column E using this
*array* formula in E1:

=COUNT(1/FREQUENCY(IF(($A$1:$A$5=C1)+($A$1:$A$5=D1),MATCH($ B$1:$B$5,$B$1:$B$5,0)+2),ROW($1:$5)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy the formula down as needed.

If you're only looking to count a single word, simply leave either Column C
or D blank.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"AJ Patel" wrote in message
...
I am trying to come up with a formula that will look at a column for a word
or set of words, look at a 2nd column and give me a running total in
another column as long as none of the values in the 2nd column match. For
example, use the following worksheet
A B C D
1 Ball 1 Ball 2
2 Bat 2 Bat 1
3 Ball 3 Glove 1
4 Ball 3
5 Glove 4
for Cell D1 I want the function to look at column A for Ball give me
a running total as long as the numbers in column B do not match, so in
this
case you can see that cell B3 and B4 both have 3, so I only need that
counted
once for
cell d1 therefore my running total would be 2. Also how can I look at
column A and look for two sets of words and give me a running total. For
example the same scenario if i want to look for "bat" and "ball" in column
A
then look at column B make sure there is nothing that matches and then
give
me a running total, in this case total would be 3. I hope I explained what
I
need clearly