count function

[AJ Patel]

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

 

[RagDyer]

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.

 

[Teethless mama]

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

 

[T. Valko]

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<>""),B1: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.

 

[RagDyeR]

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

Regards,

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

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<>""),B1: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.

 

[AJ Patel]

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.

 

[RagDyer]

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))*(B1: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.

 

[T. Valko]

See your other post