help on summing unique values

[ghee up]

Dear experts out there,

i have this data sheet that i need to consolidate.

Job Activity System Pts Taken Pts Employee
1234 20 23 15 AA
1234 20 23 8 AB
1234 30 15 15 AC
1234 60 500 100 AD
1234 60 500 200 AE
1234 60 500 200 AD
1300 20 10 10 AA
1300 30 40 20 AC
1300 60 250 150 AE
1300 60 250 100 AD

what i want to do if possible is to sum taken points for every unique Job and Activity but not the System Points.

Will it be possible to come up with this?
Job Activity System Pts Taken Pts
1234 20 23 23
1234 30 15 15
1234 60 500 500
SUM 538 538
1300 20 10 10
1300 30 40 20
1300 60 250 250
SUM 300 280

what happened is if i put this in pivot, system pts are also sum up. So instead of getting 538pts for Job 1234 i am getting a total of 1561 because it add all pts for every activity. In essence activity 20 is shared by 2 employees and activity 60 is shared by 3 employee. i have no problem with taken pts in pivot because i need the sum of it.

any help on this is greatly appreciated. as i have tons of jobs with this scenarios.

thank you in advance for your your assistance.

regards,
ghee up

 

[miss_ell]

Hi,

I'm getting it to work with a pivot table. Make sure that only the taken points (what you want to sum) is in the Data section and the other columns are in the Row section. Also, Double-click on the Activity and System Points fields and turn off the sums there. I've gotten this:-

<code>Sum of Taken Pts
Job Activity System Pts Total
1234 20 23 23
30 15 15
60 500 500
1234 Total 538
1300 20 10 10
30 40 20
60 250 250
1300 Total 280
Grand Total 818</code>


If there are any questions please let me know.

 

[Marcelo Branco]

Try this

Assuming your data in columns A: E headers in row 1

Put this formula in G2
=COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1

Let G1 empty

Type in I1, J1, K1 , respectively, Job, Activity, System

Then select your data and
Data > Adavanced Filter

pick Copy to another location

Criteria Range: Gi:G2
Copy to: I1:K1

You get the columns I , J and K of the table below

I J K L

Job	Activity	System	PtsTaken
1234	20	23	23
1234	30	15	15
1234	60	500	500
1300	20	10	10
1300	30	40	20
1300	60	250	250

<tbody>
</tbody>

Type in L1 the header PtsTaken

Formula in L2 copied down
=SUMIFS(D:D,A:A,I2,B:B,J2,C:C,K2)

And use the data table to build the Pivot Table, getting

Job	Activity	Sum of System	Sum of PtsTaken
1234	20	23	23
	30	15	15
	60	500	500
1234 Total		538	538
1300	20	10	10
	30	40	20
	60	250	250
1300 Total		300	280
Grand Total		838	818

<tbody>
</tbody>

Hope this is what you need

M.

 

[ghee up]

hi miss_ell,

thank you for your response. i made it work as you suggested however i would alos like to see the Total (sum) of the system points. As there will be instances where taken pts can be higher than the system pts.

also, if possible as there will be some instance where i just need to get the Totals System Pts, Totals Taken Pts per Job, without showing the activities. similar like below.
is this possible in pivot?

Job Name System Pts Taken Pts
1234 538 538
1300 300 280

thank you!

 

[ghee up]

hi marcelo,

thank you very much. i am not really good in formulas but after following your instructions, i made it work
another question is, as i will have dynamic data. can i just copy paste the Job Activity Sys Pts Taken Pts and the formulas will update?
thank you very much!

 

[miss_ell]

You get the columns I , J and K of the table below

I J K L

Job	Activity	System	PtsTaken
1234	20	23	23
1234	30	15	15
1234	60	500	500
1300	20	10	10
1300	30	40	20
1300	60	250	250

<TBODY>
</TBODY>

Hi Marcelo,

How do you get your table to show the borders? I've now managed to align the figures properly but I can't seem to get the borders showing.

Thanks in advance!

 

[miss_ell]

Hi ghee,

I did not know that you also wanted totals of the system points. Unfortunately I have been unable to obtain the desired figures after many attampts using the pivot table, so perhaps Marcelo's solution is what you need. Sorry about that.

 

[Marcelo Branco]

Hi Marcelo,

How do you get your table to show the borders? I've now managed to align the figures properly but I can't seem to get the borders showing.

Thanks in advance!

Hi miss_ell

If you are using MS Internet Explorer:
In Excel select the range you want to post
Put borders
Copy(Ctrl+C)
In the forum reply page: right-click and pick Paste

M.

 

[Marcelo Branco]

hi marcelo,

thank you very much. i am not really good in formulas but after following your instructions, i made it work
another question is, as i will have dynamic data. can i just copy paste the Job Activity Sys Pts Taken Pts and the formulas will update?
thank you very much!

Hi ghee,

Yes the formula will update, but you need to filter the data as i did above to get unique values for Job - Activity - System, not simply copying/pasting.

M.