See your other post

--
Biff
Microsoft Excel MVP


"AJ Patel" wrote in message
...
Thanks, now if I want to take that a little further, using the following
worksheet
A B C D
1 Bat Defective 1
2 Bat Defective 1
3 Ball Defective 2
4 Bat Defective 3
5 Bat Good 4
n Glove Defective 5

I want a running total of bat, ball, glove, using those values as a search
string, that are defective from column B, and the value in C does not
match.
"T. Valko" wrote:

A few keystrokes shorter:

For multiple crteria:

=COUNT(1/FREQUENCY(IF((A1:A5=C1)+(A1:A5=D1),B1:B5),B1:B5))

If there might be empty cells in B1:B5 those will be counted as 0. To
account for that if needed:

=COUNT(1/FREQUENCY(IF(((A1:A5=C1)+(A1:A5=D1))*(B1:B5<""),B 1:B5),B1:B5))

For a single criteria:

=COUNT(1/FREQUENCY(IF((A1:A5=C1,B1:B5),B1:B5))

=COUNT(1/FREQUENCY(IF((A1:A5=C1)*(B1:B5<""),B1:B5),B1:B5))


All formulas array entered.

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
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