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.