If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 -- OrdOff ------------------------------------------------------------------------ OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708 View this thread: http://www.excelforum.com/showthread...hreadid=382481 |
#2
|
|||
|
|||
Have I devised the best possible scenario for these issues? or does anyone have any better ideas for these formulas? Thank you OrdOff -- OrdOff ------------------------------------------------------------------------ OrdOff's Profile: http://www.excelforum.com/member.php...o&userid=22708 View this thread: http://www.excelforum.com/showthread...hreadid=382481 |
#3
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combining Text from multiple cells under multiple conditions | KNS | Worksheet Functions | 2 | June 15th, 2005 11:00 PM |
Banding with Conditional Formatting with Multiple Conditions | Geremia Doan | Worksheet Functions | 7 | February 2nd, 2005 03:14 PM |
How to create Multiple Conditional Formulas in a single cell? | Maxfx | General Discussion | 2 | February 1st, 2005 02:26 PM |
How to multiple conditions to validate more than 2 conditions to . | Bhuvana Govind | Worksheet Functions | 1 | January 28th, 2005 07:07 PM |
SUMIF with multiple conditions | vtisix | Worksheet Functions | 3 | July 6th, 2004 06:27 AM |