+ Reply to Thread
Results 1 to 38 of 38

Need Formulas for counting multiple conditions

  1. #1
    Registered User
    Join Date
    04-27-2005
    Posts
    3

    Need Formulas for counting multiple conditions

    Hopefully I can get some help with these formulas.

    I have had a little success already but I am attempting to reduce the amount of formulas.

    Here is the scenario
    The database in excel is aprox 500 lines with Row 1 as a title row
    In column A is the Position Number (101, 102, 103…)
    In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1, W2, W3…)
    In column C is a Code (PP, P1, P3, S1, R5, YY, G1)


    These are the tasks that I am attempting to complete

    Task 1
    Certain Codes are grouped together for accountability (PP,P3,S1)
    I have been able to count this group by grade by adding these three formulas together
    There is more than three on the actual sheet but for example purposes I will limit the size.

    (Array formulas)
    {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    (Basic Sum formula to add them together)

    I would like a formula to combine these formulas into one.

    Task 2
    If an individual is not assigned a Position Number and is coded with YY, or G1 or etc then he is surplus. To count these individuals by Grade I have used these formulas

    =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    (Basic Sum Formula to add them together)

    I would like a formula to combine these formulas into one

    Task 3
    A more complicated version of task one. Must combine all the ranks of Ws into one group and still group certain codes (PP, P3, S1)

    {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}

    I would like to be able to reduce this to one formula.

    Task 4
    A more complicated version of Task 2 combining the ranks of Ws into one group

    =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    (Basic Sum Formula to add them together)

    I would like to be able to reduce this to one formula

    Thank you in advance for your interest in my problem

  2. #2
    Registered User
    Join Date
    04-27-2005
    Posts
    3

    Best Possible Scenerio????

    Have I devised the best possible scenario for these issues? or does anyone have any better ideas for these formulas?

    Thank you
    OrdOff

  3. #3
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  4. #4
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  5. #5
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  6. #6
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  7. #7
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  8. #8
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  9. #9
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  10. #10
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  11. #11
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  12. #12
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  13. #13
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  14. #14
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  15. #15
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  16. #16
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  17. #17
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  18. #18
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  19. #19
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  20. #20
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  21. #21
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  22. #22
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  23. #23
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  24. #24
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  25. #25
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  26. #26
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  27. #27
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  28. #28
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  29. #29
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  30. #30
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  31. #31
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  32. #32
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  33. #33
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  34. #34
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  35. #35
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  36. #36
    bj
    Guest

    RE: Need Formulas for counting multiple conditions

    for
    (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}

    try
    =sumproduct(--(B2:B500="01"),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    for
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))

    try
    =sumproduct(--(B2:b500="01"),--(or(C2:C500=â€YYâ€,C2:C500=â€G1â€)),--(A2:A500=""))

    for
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    > try

    =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    or
    =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))

    I am not sure if this answers your second quesiton but the style should help
    you figure out what to do for the third and fourth questions.



    "OrdOff" wrote:

    >
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€O1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€O1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W1â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W2â€,(IF(C2:C500=â€S1â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€PPâ€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€P3â€,1,0))))}
    > {=SUM(IF(B2:B500=â€W3â€,(IF(C2:C500=â€S1â€,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W1â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€YYâ€)*(A2:A500=â€â€))
    > =SUMPRODUCT((B2:B500=â€W2â€)*(C2:C500=â€G1â€)*(A2:A500=â€â€))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >
    > --
    > OrdOff
    > ------------------------------------------------------------------------
    > OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708
    > View this thread: http://www.excelforum.com/showthread...hreadid=382481
    >
    >


  37. #37
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions

    Task 1:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{"PP","P3","S1"},0))+0)

    Task 2:

    =SUMPRODUCT(($B$2:$B$500=”O1”)+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    Task 3

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2","W3"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”PP”,"P3","S1"},0))+0)

    Task 4:

    =SUMPRODUCT(ISNUMBER(MATCH($B$2:$B$500,{”W1”,"W2"},0))+0,ISNUMBER(MATCH($C$2:$C$500,{”YY”,"G1"},0))+0,($A$2:$A$500=””)+0)

    OrdOff wrote:
    > Hopefully I can get some help with these formulas.
    >
    > I have had a little success already but I am attempting to reduce the
    > amount of formulas.
    >
    > Here is the scenario
    > The database in excel is aprox 500 lines with Row 1 as a title row
    > In column A is the Position Number (101, 102, 103…)
    > In column B is the Rank of the individual (O1, O2..., E1, E2, E3…,W1,
    > W2, W3…)
    > In column C is a Code (PP, P1, P3, S1, R5, YY, G1)
    >
    >
    > These are the tasks that I am attempting to complete
    >
    > Task 1
    > Certain Codes are grouped together for accountability (PP,P3,S1)
    > I have been able to count this group by grade by adding these three
    > formulas together
    > There is more than three on the actual sheet but for example purposes I
    > will limit the size.
    >
    > (Array formulas)
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”O1”,(IF(C2:C500=”S1”,1,0))))}
    > (Basic Sum formula to add them together)
    >
    > I would like a formula to combine these formulas into one.
    >
    > Task 2
    > If an individual is not assigned a Position Number and is coded with
    > YY, or G1 or etc then he is surplus. To count these individuals by
    > Grade I have used these formulas
    >
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”O1”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like a formula to combine these formulas into one
    >
    > Task 3
    > A more complicated version of task one. Must combine all the ranks of
    > Ws into one group and still group certain codes (PP, P3, S1)
    >
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W1”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W2”,(IF(C2:C500=”S1”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”PP”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”P3”,1,0))))}
    > {=SUM(IF(B2:B500=”W3”,(IF(C2:C500=”S1”,1,0))))}
    >
    > I would like to be able to reduce this to one formula.
    >
    > Task 4
    > A more complicated version of Task 2 combining the ranks of Ws into one
    > group
    >
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W1”)*(C2:C500=”G1”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”YY”)*(A2:A500=””))
    > =SUMPRODUCT((B2:B500=”W2”)*(C2:C500=”G1”)*(A2:A500=””))
    > (Basic Sum Formula to add them together)
    >
    > I would like to be able to reduce this to one formula
    >
    > Thank you in advance for your interest in my problem
    >
    >


    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

  38. #38
    Aladin Akyurek
    Guest

    Re: Need Formulas for counting multiple conditions



    bj wrote:
    [...]
    >> try

    >
    > =sumproduct(--(or(B2:B500="W1",B2:B500="W2",B2:B500="W3")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > or
    > =sumproduct(--(left(B2:B500,1)="W")),--(or(C2:C500="PP",C2:C500="P3",C2:C500="S1")))
    >
    > I am not sure if this answers your second quesiton but the style should help
    > you figure out what to do for the third and fourth questions.


    You can't call on OR (or AND) in a formula that needs to operate on
    evaluations that are arrays, not scalars (single values).

    --

    [1] The SumProduct function should implicitly coerce the truth values to
    their Excel numeric equivalents.
    [2] The lookup functions should have an optional argument for the return
    value, defaulting to #N/A in its absence.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1