count function further

[AJ Patel]

Thank you for all those that helped me with the count function for running
total. Now 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.
So basically I need to first look at colum B grab all that are defective,
then look at column A grab 1st "Bat" using "bat" as the search string, then
get a running total of all bats that are defective, that the value in column
c does not match.
For our example my results should be
bat = 2
ball = 1
glove = 1

 

[T. Valko]

Assuming no empty cells in column C.

E1:E3 = Bat, Ball, Glove

Enter this array formula** in F1 and copy down to F3:

=COUNT(1/FREQUENCY(IF((A$1:A$6=E1)*(B$1:B$6="defective"),C$1:C$6),C$1:C$6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[PCLIVE]

Try this.

For Bat:
=SUMPRODUCT(--(B1:B100="Defective"),--(A1:A100="Bat"),C1:C100)

For Ball:
=SUMPRODUCT(--(B1:B100="Defective"),--(A1:A100="Ball"),C1:C100)

For Glove:
=SUMPRODUCT(--(B1:B100="Defective"),--(A1:A100="Glove"),C1:C100)

HTH,
Paul

 

[PCLIVE]

Ok, I mis-read a little.
Try this:

For Bat:
=SUMPRODUCT(--(B1:B100="Defective"),--(A1:A100="Bat"))

For Ball:
=SUMPRODUCT(--(B1:B100="Defective"),--(A1:A100="Ball"))

For Glove:
=SUMPRODUCT(--(B1:B100="Defective"),--(A1:A100="Glove"))

Regards,
Paul

--

 

[AJ Patel]

Thanks to you both for your help

 

[T. Valko]

You're welcome.

Hopefully, one of our suggestions worked!