count function further

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AJ Patel - 11 Mar 2008 19:27 GMT

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

Reply to this Message

T. Valko - 11 Mar 2008 19:51 GMT

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)

Signature

Biff
Microsoft Excel MVP

> 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
[quoted text clipped - 20 lines]
> ball = 1
> glove = 1

Reply to this Message

PCLIVE - 11 Mar 2008 19:54 GMT

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

Reply to this Message

PCLIVE - 11 Mar 2008 19:58 GMT

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

> Try this.
>
[quoted text clipped - 36 lines]
>> ball = 1
>> glove = 1

Reply to this Message

AJ Patel - 11 Mar 2008 22:03 GMT

Thanks to you both for your help

> 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
[quoted text clipped - 17 lines]
> ball = 1
> glove = 1

Reply to this Message

T. Valko - 12 Mar 2008 02:23 GMT

You're welcome.

Hopefully, one of our suggestions worked!

Signature

Biff
Microsoft Excel MVP

> Thanks to you both for your help
>
[quoted text clipped - 25 lines]
>> ball = 1
>> glove = 1

Reply to this Message