Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
I am wanting to create a column of summed authorized quanties by group no.
For example, what is the total authorized quantity for Group 13? For Group 14? Right now they are just listed individually, but I would like to sum them by group in a new column. Please help. Thanks! A B GROUP AUTH QUANT. 13 13.50 14 135.00 14 407.00 14 946.29 25 18.00 194 1.07 194 33.80 200 192.73 200 107.41 200 202.55 200 613.78 200 613.78 370 39.99 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
=SUMIF(A2:A14,14,B2:B14)
will sum Group 14 or Put this in C2 and copy down: it will sum a gropu at first occurence of the gropu number (assumes data is sorted by group) =IF(COUNTIF($A$2:$A2,A2)=1,SUMIF($A$2:$A$14,A2,$B$ 2:$B$14),"") GROUP AUTH QUANT. Group Total 13 13.5 13.50 14 135 1488.29 14 407 14 946.29 25 18 18.00 194 1.07 34.87 194 33.8 200 192.73 1730.25 200 107.41 200 202.55 200 613.78 200 613.78 370 39.99 39.99 HTH "Lisa S." wrote: I am wanting to create a column of summed authorized quanties by group no. For example, what is the total authorized quantity for Group 13? For Group 14? Right now they are just listed individually, but I would like to sum them by group in a new column. Please help. Thanks! A B GROUP AUTH QUANT. 13 13.50 14 135.00 14 407.00 14 946.29 25 18.00 194 1.07 194 33.80 200 192.73 200 107.41 200 202.55 200 613.78 200 613.78 370 39.99 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
Thank you so much.
"Toppers" wrote: =SUMIF(A2:A14,14,B2:B14) will sum Group 14 or Put this in C2 and copy down: it will sum a gropu at first occurence of the gropu number (assumes data is sorted by group) =IF(COUNTIF($A$2:$A2,A2)=1,SUMIF($A$2:$A$14,A2,$B$ 2:$B$14),"") GROUP AUTH QUANT. Group Total 13 13.5 13.50 14 135 1488.29 14 407 14 946.29 25 18 18.00 194 1.07 34.87 194 33.8 200 192.73 1730.25 200 107.41 200 202.55 200 613.78 200 613.78 370 39.99 39.99 HTH "Lisa S." wrote: I am wanting to create a column of summed authorized quanties by group no. For example, what is the total authorized quantity for Group 13? For Group 14? Right now they are just listed individually, but I would like to sum them by group in a new column. Please help. Thanks! A B GROUP AUTH QUANT. 13 13.50 14 135.00 14 407.00 14 946.29 25 18.00 194 1.07 194 33.80 200 192.73 200 107.41 200 202.55 200 613.78 200 613.78 370 39.99 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
Lisa/Toppers--Maybe I am missing something--but this is a routine task
in Excel. Instead of using formulas as Toppers recommends, which will give Lisa the correct answer, but is harder for Lisa to learn and apply to the next situation, I would: Highlight the range with the Titles and Data/Sort by Group in Ascending order.. Then I would highlight the sorted range--Data/Subtotal by Group. In the Subtotal dialog box I would check the box "Group" "at each change in" and check the box "sum" under the "Use function" and check the box quantity under the words "Add Subtotal to." This is what you get. Group Qty 13 13.5 13 Total 13.5 14 135 14 407 14 946.29 14 Total 1488.29 25 18 25 Total 18 194 1.07 194 33.8 194 Total 34.87 200 192.7 200 107.4 200 202.5 200 613.7 200 613.7 200 Total 1730 370 39.99 370 Total 39.99 Grand Total 3324.65 Toppers--no disrespect intended--it is obvious to all that you are extremely knowledgeable about Excel. Steve G |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
No offence taken and I agree that it makes sense to use "built-in"
functionality rather re-invent the wheel. I rarely use the Subtotal function total so never considered it. "Steve G" wrote: Lisa/Toppers--Maybe I am missing something--but this is a routine task in Excel. Instead of using formulas as Toppers recommends, which will give Lisa the correct answer, but is harder for Lisa to learn and apply to the next situation, I would: Highlight the range with the Titles and Data/Sort by Group in Ascending order.. Then I would highlight the sorted range--Data/Subtotal by Group. In the Subtotal dialog box I would check the box "Group" "at each change in" and check the box "sum" under the "Use function" and check the box quantity under the words "Add Subtotal to." This is what you get. Group Qty 13 13.5 13 Total 13.5 14 135 14 407 14 946.29 14 Total 1488.29 25 18 25 Total 18 194 1.07 194 33.8 194 Total 34.87 200 192.7 200 107.4 200 202.5 200 613.7 200 613.7 200 Total 1730 370 39.99 370 Total 39.99 Grand Total 3324.65 Toppers--no disrespect intended--it is obvious to all that you are extremely knowledgeable about Excel. Steve G |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
Steve G wrote...
Lisa/Toppers--Maybe I am missing something--but this is a routine task in Excel. Instead of using formulas as Toppers recommends, which will give Lisa the correct answer, but is harder for Lisa to learn and apply to the next situation, . . . That last clause is condescending & judgmental. Not bad! Stated intent not to offend Toppers, but no such intent re the OP. . . . I would: Highlight the range with the Titles and Data/Sort by Group in Ascending order.. OP's data already sorted. If OP's data not sorted, what would you do if the OP couldn't sort by Group without screwing up other formulas? Then I would highlight the sorted range--Data/Subtotal by Group. .... I might not feel so strongly about this if I didn't spend so much of my typical work week DELETING the Oh! So! Helpful! grouping and subtotals other people add to workbooks they send me that I want/need to use as flat files. There is the off chance the OP needs to use this table for more than just summarizing quantities by group. If so, adding grouping and subtotals will almost certainly fubar those other tasks. If all the OP needs to do with this is summarize it, then grouping and subtotals may be OK. Otherwise, Topper's formulas would be a better idea. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
Mr. Grove and all others particularly Lisa S--If I was judgmental and
condescending to Lisa I truly apologize. I did not mean to me. From reading Lisa's question I thought she wanted a simple task done for which Excel has a built in function. Lisa did not state that she had formulas in her cells so therefore I did not presume that she did. I do not know Lisa's capability in Excel. Quite frankly I thought she might be a newbie. But I am not judging her on that or anything else. I am a newbie to VBA so I know what it is like to be a newbie. Therefore I explained step by step how to use the sort and subtotal dialogs in Excel. No harm intended and if I did offend anyone, I apologize. Steve G |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
Hi John
Like you, I rarely use the Group/Subtotal function. However, there is another inbuilt function which will satisfy Lisa's requirement, and not require any Sorting or Sub-totalling (which can mess up source data layout as Harlan has described). The function is Consolidate. Place cursor in any empty cell of the sheet. DataConsolidatemark the relevant columns of datacheck Use labels in left columnOK -- Regards Roger Govier "Toppers" wrote in message ... No offence taken and I agree that it makes sense to use "built-in" functionality rather re-invent the wheel. I rarely use the Subtotal function total so never considered it. "Steve G" wrote: Lisa/Toppers--Maybe I am missing something--but this is a routine task in Excel. Instead of using formulas as Toppers recommends, which will give Lisa the correct answer, but is harder for Lisa to learn and apply to the next situation, I would: Highlight the range with the Titles and Data/Sort by Group in Ascending order.. Then I would highlight the sorted range--Data/Subtotal by Group. In the Subtotal dialog box I would check the box "Group" "at each change in" and check the box "sum" under the "Use function" and check the box quantity under the words "Add Subtotal to." This is what you get. Group Qty 13 13.5 13 Total 13.5 14 135 14 407 14 946.29 14 Total 1488.29 25 18 25 Total 18 194 1.07 194 33.8 194 Total 34.87 200 192.7 200 107.4 200 202.5 200 613.7 200 613.7 200 Total 1730 370 39.99 370 Total 39.99 Grand Total 3324.65 Toppers--no disrespect intended--it is obvious to all that you are extremely knowledgeable about Excel. Steve G |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
On Aug 28, 6:50 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote: Hi John Like you, I rarely use the Group/Subtotal function. However, there is another inbuilt function which will satisfy Lisa's requirement, and not require any Sorting or Sub-totalling (which can mess up source data layout as Harlan has described). The function is Consolidate. Place cursor in any empty cell of the sheet. DataConsolidatemark the relevant columns of datacheck Use labels in left columnOK -- Regards Roger Govier "Toppers" wrote in message ... No offence taken and I agree that it makes sense to use "built-in" functionality rather re-invent the wheel. I rarely use the Subtotal function total so never considered it. "Steve G" wrote: Lisa/Toppers--Maybe I am missing something--but this is a routine task in Excel. Instead of using formulas as Toppers recommends, which will give Lisa the correct answer, but is harder for Lisa to learn and apply to the next situation, I would: Highlight the range with the Titles and Data/Sort by Group in Ascending order.. Then I would highlight the sorted range--Data/Subtotal by Group. In the Subtotal dialog box I would check the box "Group" "at each change in" and check the box "sum" under the "Use function" and check the box quantity under the words "Add Subtotal to." This is what you get. Group Qty 13 13.5 13 Total 13.5 14 135 14 407 14 946.29 14 Total 1488.29 25 18 25 Total 18 194 1.07 194 33.8 194 Total 34.87 200 192.7 200 107.4 200 202.5 200 613.7 200 613.7 200 Total 1730 370 39.99 370 Total 39.99 Grand Total 3324.65 Toppers--no disrespect intended--it is obvious to all that you are extremely knowledgeable about Excel. Steve G- Hide quoted text - - Show quoted text - Mr. Govier-- Sounds like you have a great procedure that I would like to learn. Please add in a few steps for me. I tried but it did not work. I placed my cursor in an empty cell. I clicked Data then I clicked Consolidate and got the Consolidated dialog box. For "References" I put in a contiguous range of numbers such as C3:C12. I set the function at "Sum." I checked the box for "use labels in left column." I then clicked "Add." I then clicked "OK." Please tell me what I did wrong. Thank you. Steve G |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
Hi Steve
It's Roger, not Mr Govier<bg With the following in A1:14 1,2,3,1,3,1,3,3,2,1,4,3,2,1,3,2 and in B1:B14 10,20,30, ....... 140 Place cursor in F1 DataConsolidate FunctionSum Reference$A$1:$B$14 Use Labels inLeft Column OK should produce F1:F4 1,2,3,4 G1:G4 250,340,370,90 I think your problem was that you only selected a single column of values, which would be the labels. There was no "Data" in your example. -- Regards Roger Govier "Steve G" wrote in message ups.com... On Aug 28, 6:50 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi John Like you, I rarely use the Group/Subtotal function. However, there is another inbuilt function which will satisfy Lisa's requirement, and not require any Sorting or Sub-totalling (which can mess up source data layout as Harlan has described). The function is Consolidate. Place cursor in any empty cell of the sheet. DataConsolidatemark the relevant columns of datacheck Use labels in left columnOK -- Regards Roger Govier "Toppers" wrote in message ... No offence taken and I agree that it makes sense to use "built-in" functionality rather re-invent the wheel. I rarely use the Subtotal function total so never considered it. "Steve G" wrote: Lisa/Toppers--Maybe I am missing something--but this is a routine task in Excel. Instead of using formulas as Toppers recommends, which will give Lisa the correct answer, but is harder for Lisa to learn and apply to the next situation, I would: Highlight the range with the Titles and Data/Sort by Group in Ascending order.. Then I would highlight the sorted range--Data/Subtotal by Group. In the Subtotal dialog box I would check the box "Group" "at each change in" and check the box "sum" under the "Use function" and check the box quantity under the words "Add Subtotal to." This is what you get. Group Qty 13 13.5 13 Total 13.5 14 135 14 407 14 946.29 14 Total 1488.29 25 18 25 Total 18 194 1.07 194 33.8 194 Total 34.87 200 192.7 200 107.4 200 202.5 200 613.7 200 613.7 200 Total 1730 370 39.99 370 Total 39.99 Grand Total 3324.65 Toppers--no disrespect intended--it is obvious to all that you are extremely knowledgeable about Excel. Steve G- Hide quoted text - - Show quoted text - Mr. Govier-- Sounds like you have a great procedure that I would like to learn. Please add in a few steps for me. I tried but it did not work. I placed my cursor in an empty cell. I clicked Data then I clicked Consolidate and got the Consolidated dialog box. For "References" I put in a contiguous range of numbers such as C3:C12. I set the function at "Sum." I checked the box for "use labels in left column." I then clicked "Add." I then clicked "OK." Please tell me what I did wrong. Thank you. Steve G |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
On Aug 28, 6:53 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote: Hi Steve It's Roger, not Mr Govier<bg With the following in A1:14 1,2,3,1,3,1,3,3,2,1,4,3,2,1,3,2 and in B1:B14 10,20,30, ....... 140 Place cursor in F1 DataConsolidate FunctionSum Reference$A$1:$B$14 Use Labels inLeft Column OK should produce F1:F4 1,2,3,4 G1:G4 250,340,370,90 I think your problem was that you only selected a single column of values, which would be the labels. There was no "Data" in your example. -- Regards Roger Govier "Steve G" wrote in message ups.com... On Aug 28, 6:50 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote: Hi John Like you, I rarely use the Group/Subtotal function. However, there is another inbuilt function which will satisfy Lisa's requirement, and not require any Sorting or Sub-totalling (which can mess up source data layout as Harlan has described). The function is Consolidate. Place cursor in any empty cell of the sheet. DataConsolidatemark the relevant columns of datacheck Use labels in left columnOK -- Regards Roger Govier "Toppers" wrote in message ... No offence taken and I agree that it makes sense to use "built-in" functionality rather re-invent the wheel. I rarely use the Subtotal function total so never considered it. "Steve G" wrote: Lisa/Toppers--Maybe I am missing something--but this is a routine task in Excel. Instead of using formulas as Toppers recommends, which will give Lisa the correct answer, but is harder for Lisa to learn and apply to the next situation, I would: Highlight the range with the Titles and Data/Sort by Group in Ascending order.. Then I would highlight the sorted range--Data/Subtotal by Group. In the Subtotal dialog box I would check the box "Group" "at each change in" and check the box "sum" under the "Use function" and check the box quantity under the words "Add Subtotal to." This is what you get. Group Qty 13 13.5 13 Total 13.5 14 135 14 407 14 946.29 14 Total 1488.29 25 18 25 Total 18 194 1.07 194 33.8 194 Total 34.87 200 192.7 200 107.4 200 202.5 200 613.7 200 613.7 200 Total 1730 370 39.99 370 Total 39.99 Grand Total 3324.65 Toppers--no disrespect intended--it is obvious to all that you are extremely knowledgeable about Excel. Steve G- Hide quoted text - - Show quoted text - Mr. Govier-- Sounds like you have a great procedure that I would like to learn. Please add in a few steps for me. I tried but it did not work. I placed my cursor in an empty cell. I clicked Data then I clicked Consolidate and got the Consolidated dialog box. For "References" I put in a contiguous range of numbers such as C3:C12. I set the function at "Sum." I checked the box for "use labels in left column." I then clicked "Add." I then clicked "OK." Please tell me what I did wrong. Thank you. Steve G- Hide quoted text - - Show quoted text - Roger-- I got it to work and will be very useful--interesting that I do not see any trace of formula after I use the 'Consolidate" function. I have tried to use "data consolidate" when working with multiple worksheets that were identical in range layout but have not gotten it to work--maybe your " written by John Walkenbach he does not mention Data--Consolidate at all. Thanks again. Steve G |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing
Hi Steve
No, you will not see any formula, just the result. If you need to refresh, then with the cursor in the first of the result cells, just choose DataConsolidateOK. It does work with multiple sheets also. You just need to keep adding the ranges until you have the range from each sheet entered, before you press OK. -- Regards Roger Govier Roger-- I got it to work and will be very useful--interesting that I do not see any trace of formula after I use the 'Consolidate" function. I have tried to use "data consolidate" when working with multiple worksheets that were identical in range layout but have not gotten it to work--maybe your " written by John Walkenbach he does not mention Data--Consolidate at all. Thanks again. Steve G |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing | Excel Worksheet Functions | |||
PivotTable and summing/not summing | Excel Discussion (Misc queries) | |||
summing up | Charts and Charting in Excel | |||
Summing | Excel Worksheet Functions | |||
Help with summing | Excel Worksheet Functions |