ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Need Formulas for counting multiple conditions (https://www.excelbanter.com/excel-worksheet-functions/32669-need-formulas-counting-multiple-conditions.html)

OrdOff
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

OrdOff

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

bj
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

Aladin Akyurek
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.

Aladin Akyurek


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.


All times are GMT +1. The time now is 03:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com