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

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

In D1:
=SUM(N(FREQUENCY(IF($A$1:$A$5=C1,MATCH($B$1:$B$5,$ B$1:$B$5,0)),MATCH($B$1:$B$5,$B$1:$B$5,0))0))

ctrl+shift+enter, not just enter
copy down


"AJ Patel" wrote:

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

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

Yep! .. a little shorter.<bg
--

Regards,

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

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

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

Building of Biff's shorter version, using D1, E1, and F1 as cells to contain
the search words, try this *array* formula in G1:

=COUNT(1/FREQUENCY(IF(((A1:A6=D1)+(A1:A6=E1)+(A1:A6=F1))*(B 1:B6="Defective"),C1:C6),C1:C6))

--
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.

--
HTH,

RD

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

"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

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