# help on summing unique values

#### ghee up

##### New Member
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 200 AE
1300 20 10 10 AA
1300 30 40 20 AC
1300 60 250 150 AE

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.

regards,
ghee up

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.

Last edited:
Try this

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

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.

Last edited:
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!

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!

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.

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.

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.

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.

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.

Last edited:

Replies
5
Views
270
Replies
2
Views
159
Replies
1
Views
209
Replies
1
Views
160
Replies
1
Views
179

1,196,057
Messages
6,013,160
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back