How To Sum A Column Against Each Unique ID With Multiple Conditions

[kashif.special2005]

Hi,

I have a sample data as below, original data is around 90 thousand.

Please Note:- That in the below sample data the value in the column "Approval Status" is show "Approved", but this is not the case it can be other values as well for example like "Rejected" or "Submitted" etc.,

Sample Data:-

[TABLE="width: 413"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Category[/TD]
[TD]Number of Hours[/TD]
[TD]Approval Status[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Training[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Training[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Absence[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Standard Hours[/TD]
[TD]0[/TD]
[TD]Approved

[/TD]
[/TR]
</tbody>[/TABLE]

And I want to create the output as below for each unique ID's, below is the sample for 1 ID's

Desired Output.

[TABLE="width: 581"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]CPAR[/TD]
[TD]PPAR[/TD]
[TD]HBAR[/TD]
[TD]ODAR[/TD]
[TD]CPAR + PPAR[/TD]
[TD]HBAR:Absence[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


Conditions For Calculating the columns "CPA, PPAR, HBAR, ODAR, CPAR + PPAR And HBAR:Absence"


Condition(1) - CPAR (Column):- Calculating Condition

if column "Approval Status=Approved" and column "Category=Revenue, or Category=Cost
Recovery" then take the sum of column "Number of Hours" and put the value in the column
B against the ID.


Condition(2) - PPAR (Column):- Calculating Condition

if column "Approval Status=Approved" and column "Category=Sales Sales Support" then
take the sum of column "Number of Hours" and put the value in the column C against the ID.


Condition(3) - ODAR (Column):- Calculating Condition

if column "Approval Status=Approved" and column "Category=Training" then take the sum of
column "Number of Hours" and put the value in the column E against the ID.


Condition(4) - HBAR (Column):- Calculating Condition

if Above conditions (1, 2 And 3) are not meet and column "Approval Status<>Approved" and
column "Category<>Absence" then take the sum of column "Number of Hours" and put the
value in the column D against the D.


Condition(5) - HBAR:Absence (Column):- Calculating Condition

if Above all three condition (1, 2 And 3) are not meet and column "Approval
Status=Approved" and column "Category=Absence" then take the sum of column "Number of
Hours" and put the value in the column G against the ID.


Condition(6) - CPAR + PPAR (Column):- Calculating Condition

Simply put the sum of column B (CPAR) + Column C (PPAR)


I don't have any idea how can I achieve desired result, my head is spinning to think about that.

Kindly request to all of excel vba champs, please help me to achieve this task.

Many thanks in advance

Thanks
Kashif

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][td="bgcolor:#C0C0C0"]

M​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

ID​

[/td][td="bgcolor:#F3F3F3"]

CPAR​

[/td][td="bgcolor:#F3F3F3"]

PPAR​

[/td][td="bgcolor:#F3F3F3"]

HBAR​

[/td][td="bgcolor:#F3F3F3"]

ODAR​

[/td][td="bgcolor:#F3F3F3"]

CPAR+PPAR​

[/td][td="bgcolor:#F3F3F3"]

HBAR:Absence​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

69033154​

[/td][td]

72​

[/td][td]

48​

[/td][td]

0​

[/td][td]

12​

[/td][td]

120​

[/td][td]

???​

[/td][td]G2: =SUM(SUMIFS($C$2:$C$27, $A$2:$A$27, $F2, $B$2:$B$27, {"Revenue","Cost Recovery"}, $D$2:$D$27, "Approved"))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]H2: =SUMIFS($C$2:$C$27, $A$2:$A$27, $F2, $B$2:$B$27, "Sales Sales Support", $D$2:$D$27, "Approved")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]I2: =SUMIFS($C$2:$C$27, $A$2:$A$27, $F2, $B$2:$B$27, "<>Absence", $D$2:$D$27, "<>Approved")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]J2: =SUMIFS($C$2:$C$27, $A$2:$A$27, $F2, $B$2:$B$27, "Training", $D$2:$D$27, "Approved")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]K2: =G2+H2[/td][/tr]
[/table]

 

[kashif.special2005]

Hi shg,

Thank you thank you so much for reply.

For column HBAR:Absence the calculation method is

if the conditions 1, 2 And 3 are not meet and the column "Approval
Status=Approved" and column "Category=Absence" then take the sum of column "Number of
Hours" and put the value in the column G against the ID.

The value for the column "HBAR" is showing 0, however as per the data the HBAR value should be 20, and for the column HBAR:Absence value should be 8.

For HBAR:- The category should not be "Absence" and Approval Status should not be "Approved"

in the conditions 1, 2 and 3 we have not used the value in the columns category "Business Support", so for HBAR this value will sum for the column "HBAR"


Or you can say that

Condition(4) - HBAR (Column):- Calculating Condition

if Above conditions (1, 2 And 3) are not meet the take the sum of column "Number of Hours" and put the value in the column D against the D.

And I have one request as well can we don't do with VBA because for this first I have to copy the unique ID in columns A, and in the formula you are using the column positions as fixed like for ID column you are considering column A, but in the future the columns positions may change.

So I am requesting you can we do this VBA?

Thanks
Kashif

 

[kashif.special2005]

Condition(4) - HBAR (Column):- Calculating Condition

if Above conditions (1, 2 And 3) are not meet the take the sum of column "Number of Hours" and put the value in the column D against the ID.

 

[kashif.special2005]

Hi All,

I have Added a sample column "Manually Putting Conditional Values For Understanding" in the end for better understanding.


[TABLE="width: 530"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Category[/TD]
[TD]Number of Hours[/TD]
[TD]Approval Status[/TD]
[TD]Manually Putting Conditional Values For Understanding[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Training[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]ODAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Training[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]ODAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]HBAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]HBAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]HBAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]PPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]PPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]PPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]PPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]PPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Sales Sales Support[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]PPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Absence[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]HBAR:Absence[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]HBAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Business Support[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]HBAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]8[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Revenue[/TD]
[TD]4[/TD]
[TD]Approved[/TD]
[TD]CPAR[/TD]
[/TR]
[TR]
[TD]69033154[/TD]
[TD]Standard Hours[/TD]
[TD]0[/TD]
[TD]Approved[/TD]
[TD]HBAR[/TD]
[/TR]
</tbody>[/TABLE]

I putted those value as per the below conditions

Condition(1) - CPAR (Column):- Calculating Condition

if column "Approval Status=Approved" and column "Category=Revenue, or Category=Cost
Recovery" then it will be CPAR.


Condition(2) - PPAR (Column):- Calculating Condition

if column "Approval Status=Approved" and column "Category=Sales Sales Support" then it will be PPAR.


Condition(3) - ODAR (Column):- Calculating Condition

if column "Approval Status=Approved" and column "Category=Training" then it will be ODAR.

Condition(4) - HBAR (Column):- Calculating Condition

If above conditions (1, 2 and 3) are not met then HBAR and in the HBAR Staus if "Approval Status=Approved" and column "Category=Absence" then then HBAR:Absence

I hope it will help to understand better.

 

[kashif.special2005]

Hi,

Please all excel experts please help me to solve this problem.

I google for VBA code but I could not find anything as per my requirement, I saw similar kind of problem solved the VBA Dictionary, but I don't know how to use Dictionary in VBA.

Please help me.


Thanks in advance.

Thanks
Kashif