How do i get count of values against specific names

Loren.dsouza74476

I have three columns . What i need is a formula to get details - If range responsible person is selected, what is the sum of target  for each person seperately and in a similar way on the actual column what is the sum of actual by each person. The result i am look for should look like the one below. 

responsible person       Target   Actual

Peter                               1           0

Peter                                2           1

Ben                                  1           1

Kylie                                  1         0

Sam                                  1          0

Kylie                                  3          0

 

---------------------------------------------------------

Peter                                 3           1

Ben                                    1           1

Kylie                                   4           0

Sam                                   1           0

 

Andrée Starå

Hi Loren,

Try this.

The below formula is for the Target but can easily be changed to SUM the Actual as well. Change the row numbers and column names to your own. I would recommend using Parent/Children for the set up if possible instead because it will probably make it easier to keep current. 

=SUMIF([Responsible Person]8:[Responsible Person]13; [Responsible Person]@row; Target8:Target13)    

The same version but with the below changes for your and others convenience.  

=SUMIF([Responsible Person]8:[Responsible Person]13, [Responsible Person]@row, Target8:Target13)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting