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