Subtotal Multiple Columns

[DanaK]

Why can't I figure this out!?!? It seems so simple....
I have three columns listing GL#, ST# & RL. The last column contains the
value to be allocated to that string. How can I subtotal by the first three
columns at the same time?
GL ST RL Amount
602600 05 HA 3.68
602600 05 HA 4.82
602600 05 HB 1.32
602600 05 HB 1.16
602600 05 HC 0.53
602600 05 HC 0.49
602600 05 HE 1.05
602600 05 HE 0.14
602600 05 KA 0.96
602600 05 KA 2.70
602600 06 HA 0.84
602600 06 KA 0.84
602600 08 HA 0.28
602600 09 HA 0.28
602600 09 KA 0.51
751100 05 CC 2.81

In the end I need one total for each possible GL,ST,RL combination.
GL ST RL Amount
602600 05 HA 8.50
602600 05 HB 2.48
602600 05 HC 1.01
602600 05 HE 1.19
602600 05 KA 3.65
602600 06 HA 0.84
602600 06 KA 0.84
602600 08 HA 0.28
602600 09 HA 0.28
602600 09 KA 0.51
751100 05 CC 2.81

There are thousands of combinations and the allocations run about 13,000
rows. I'd appreciate any help. Pivots aren't working for me on this.

Thank you!
DanaK

 

[Jacob Skaria]

Use pivot TABLE

Refer the below link for help and once you get into the 'Dragging and
Dropping Data' drag and drop the first 3 field to the 'Row Field'
Drag amount field to 'Data Item' field.

http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx


If this post helps click Yes

 

[DanaK]

Thanks, Jacob. Pivots nor subtotals can't return a single row for each unique
string without the groups/outlines which are more of a hassle to work with
for the final application of the values. My work around for this has been to
concatenate the string into one field and subtotal on that field. I was
hoping there was a formula available!

 

[Francisco CABRERA]

this will answer how...

http://www.eggheadcafe.com/communit...tiple-columns-at-each-change-in-a-column.aspx


content:
Subtotal Multiple Columns at Each Change in a Column

You want to summarize multiple columns,
at each change in a base column, like Subtotal (in the Data menu).


Do not get scared, It is very easy!!
Come on, Just follow my steps...


Suppose, we have the following data table:
Month Department Salary Tax Discount Neto
January Human Resources 1,000.00 -150.00 -250.00 600.00
January IT Department 3,000.00 -350.00 -450.00 2,200.00
January Accounting 2,000.00 -250.00 -350.00 1,400.00
February Human Resources 1,000.00 -150.00 -270.00 580.00
February IT Department 3,000.00 -350.00 -470.00 2,180.00
February Accounting 2,000.00 -250.00 -370.00 1,380.00
March Human Resources 1,000.00 -150.00 -290.00 560.00
March IT Department 3,000.00 -350.00 -490.00 2,160.00
March Accounting 2,000.00 -250.00 -390.00 1,360.00

And we want to summarize Salary, Tax, Discount and Total for each Department
for the 3 months of the example,
in order to know those amounts for the quarter of the year

Focus that Department has repeated values, so we need to group by this column,
and it will be our base column or control column, whatever you want to call it.
Look that the first column is located at the most left side of
the data amounts we want to summarize. So in your case, organize your data in that way.


Let's begin. First, click on the column labeled Department,
then navigate to the Data menu, click on PivotTable, then

on the window, activate Multiple consolidation ranges, then click on the [NEXT] button

on the next window, click on Create a single page field for me, then click on the [NEXT] button

the next window is asking the Range,
click on the Header titles, selecting the one labeled Department to the most right header,
and go down to the of the data table, then click on the [FINISH] button.


Now, we have Pivot table with all the data, grouping by the column control (Department)

If you see a cell labeled "Count of value",
right click on it and in the popup menu select Field Settings, on the section Summarize by,
click Sum and you got it,

Congratulations!!
now you know how to summarize multiple columns, base on a column control.