Sum Down in Pivot Table With The Same Name as Criteria

[muhammad susanto]

hi all...

i need your help, how to setting/make it in pivot table like this sample below :

Excel 2007 32 bit

	A​	B​	C​	D​	E​	F​
1​	date	name	amount	transact-1	transact-2	transact-3
2​	15/10/2015​	jack	100​
3​	15/10/2015​	mike	150​
4​	15/10/2015​	fikka	200​
5​	15/10/2015​	thomas	400​
6​	12/12/2015​	mike		50​
7​	15/01/2016​	mike			10​
8​	03/03/2016​	jack				25​
9​
10​	after using pvot table (e.g.)
11​	date	name	sum of oustanding
12​	15/10/2015​	jack	100​
13​	03/03/2016​	jack	75​
14​	15/10/2015​	fikka	200​
15​	15/10/2015​	mike	150​
16​	12/12/2015​	mike	100​
17​	15/01/2016​	mike	90​
18​
19​	note :
20​	sum of oustanding = amount- (transact1+transact2+transact3)
21​	for example :
22​	for jack = 100-25=75
23​	etc..

Sheet: Sheet1

any help, much assistance..

m.susanto

 

[RoryA]

You can't get exactly that output as the dates will be grouped first. If you can have the names first so that you get them grouped, then I think you can just create a calculated field that subtracts the three transact fields from the Amount field and display the result as a running total.

 

[muhammad susanto]

You can't get exactly that output as the dates will be grouped first. If you can have the names first so that you get them grouped, then I think you can just create a calculated field that subtracts the three transact fields from the Amount field and display the result as a running total.

thank Rory, i have tring but not work, i hope you could help me
here's my link
https://app.box.com/s/adw6fye72zfq9d2r44tdum80m4ufkwcg

 

[RoryA]

What did you try? There isn't even a pivot table in that workbook.

 

[muhammad susanto]

What did you try? There isn't even a pivot table in that workbook.

sorry Rory,here's this link

https://app.box.com/s/adw6fye72zfq9d2r44tdum80m4ufkwcg

 

[RoryA]

The only row fields you should have are the name and the date, in that order. The only data field should be the Summing field. Ungroup the date field, then in the settings for the Summing field choose to Show Values As, Running Total in the date field.

 

[muhammad susanto]

The only row fields you should have are the name and the date, in that order. The only data field should be the Summing field. Ungroup the date field, then in the settings for the Summing field choose to Show Values As, Running Total in the date field.

i Rory, i have do your suggestion but stil not working
i mean the summing not correct...
i really-really need your help, do you make for me, please...

 

[RoryA]

In what way is the summing not correct? (it is more helpful for you to learn than for me to simply give it to you)

 

[muhammad susanto]

In what way is the summing not correct? (it is more helpful for you to learn than for me to simply give it to you)

the final answer, in column summing not that's i want like in post #1
e.g


15/10/2015, jack,100
03/03/2016, jack, 75

 

[RoryA]

You have to put the name as the first row field and then the date. It will not work in the original layout you requested, as I mentioned to begin with.

Actually, I stand corrected - as long as the Summing field is set to display as a running total within date, it will work with the row fields in either order.