Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Multiple Columns
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Multiple Columns
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/us...s_collins.mspx If this post helps click Yes --------------- Jacob Skaria "DanaK" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Multiple Columns
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! -- DanaK "Jacob Skaria" wrote: 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/us...s_collins.mspx If this post helps click Yes --------------- Jacob Skaria "DanaK" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Multiple Columns
this will answer how...
http://www.eggheadcafe.com/community...-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. On Wednesday, August 26, 2009 11:25 PM DanaK wrote: Why cannot 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 are not working for me on this. Thank you! DanaK On Thursday, August 27, 2009 12:44 AM Jacob Skaria wrote: 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/us...s_collins.mspx If this post helps click Yes --------------- Jacob Skaria "DanaK" wrote: On Thursday, August 27, 2009 12:36 PM DanaK wrote: Thanks, Jacob. Pivots nor subtotals cannot 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! -- DanaK "Jacob Skaria" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal show more columns | Excel Discussion (Misc queries) | |||
Subtotal - Limit in the number of columns? | Excel Discussion (Misc queries) | |||
subtotal on 2 columns | Excel Discussion (Misc queries) | |||
Subtotal and hidden columns | Excel Discussion (Misc queries) | |||
Subtotal by two columns at once | Excel Discussion (Misc queries) |