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
|