I have an Account Receivable report with columns for current, 30, 60, 90 and 120 days. I created a pivot table based off this report. I am trying to get the grand total for the customer. I'm not sure if I need to add this to my source data or can I do this within the pivot table. Here is the source data.
Here is my pivot table.
Here is what I want my pivot table to look like:
Is this possible?
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Cust | Order | Current | 30 Days | 60 Days | 90 Days | >120 Days | ||
2 | ABC | 3900 | 100 | ||||||
3 | ABC | 3901 | 200 | ||||||
4 | ABC | 3902 | 300 | ||||||
5 | ABC | 3903 | 400 | ||||||
6 | ABC | 3904 | 500 | ||||||
7 | DEF | 3905 | 600 | ||||||
8 | DEF | 3906 | 700 | ||||||
Sheet1 |
Here is my pivot table.
Row Labels | Sum of Current | Sum of 30 Days | Sum of 60 Days | Sum of 90 Days | Sum of >120 Days |
ABC | |||||
3900 | 100 | ||||
3901 | 200 | ||||
3902 | 300 | ||||
3903 | 400 | ||||
3904 | 500 | ||||
ABC Total | 100 | 200 | 300 | 400 | 500 |
DEF | |||||
3905 | 600 | ||||
3906 | 700 | ||||
DEF Total | 700 | 600 | |||
Grand Total | 100 | 900 | 300 | 1000 | 500 |
Here is what I want my pivot table to look like:
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Row Labels | Total | Sum of Current | Sum of 30 Days | Sum of 60 Days | Sum of 90 Days | Sum of >120 Days | ||
2 | ABC | ||||||||
3 | 3900 | 100 | |||||||
4 | 3901 | 200 | |||||||
5 | 3902 | 300 | |||||||
6 | 3903 | 400 | |||||||
7 | 3904 | 500 | |||||||
8 | ABC Total | 1500 | 100 | 200 | 300 | 400 | 500 | ||
9 | DEF | ||||||||
10 | 3905 | 600 | |||||||
11 | 3906 | 700 | |||||||
12 | DEF Total | 1300 | 700 | 600 | |||||
13 | Grand Total | 2800 | 100 | 900 | 300 | 1000 | 500 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B8,B12:B13 | B8 | =SUM(C8:G8) |
Is this possible?