Sumproduct ?? Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carl - 16 Mar 2006 17:49 GMT

My data comes in like this:

Type    ID    Group
B    BOX017    VR
A    BOX017    S1
B    BOX017    S1
A    BOX234    S1
B    BOX017    S1
B    BOX017    GK

I am trying to find a formula for cell B2 below that will count the number
of occurances of Type=A, ID=BOX017, and Group=S1.

ID    Type A
BOX017    1

Thank you in advance.

Reply to this Message

Ardus Petus - 16 Mar 2006 18:14 GMT

=SUMPRODUCT(--(A2:A7]=""A""),--(B2:B7]=""BOX017""),--(C2:C7]=""S1""))

HTH
--
AP

> My data comes in like this:
>
[quoted text clipped - 13 lines]
>
> Thank you in advance.

Reply to this Message

Tom Ogilvy - 16 Mar 2006 19:11 GMT

Whats up with the square bracked and the double double quotes?

=SUMPRODUCT(--(A2:A7="A"),--(B2:B7="BOX017"),--(C2:C7="S1"))

Signature

Regards,
Tom Ogilvy

> =SUMPRODUCT(--(A2:A7]=""A""),--(B2:B7]=""BOX017""),--(C2:C7]=""S1""))
>
[quoted text clipped - 19 lines]
> >
> > Thank you in advance.

Reply to this Message

Herbert Seidenberg - 16 Mar 2006 19:46 GMT

If you need to make several searches and
you want to save typing,
enter this formula below Countx:
=SUMPRODUCT((Type=TypeX R)*(ID=IDx R)*(Group=GroupX R))
Formula is in R1C1 style.
Arrange your search data this way and name it:
TypeX    IDx    GroupX    Countx
A    BOX017    S1    1
B    BOX017    S1    2

Reply to this Message