Summing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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
 
Thank you so much.

Toppers said:
=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
Click to expand...
 
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
 
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 said:
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, . . .
Click to expand...

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..
Click to expand...

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.
Click to expand...
....

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.
 
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
 
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.
Data>Consolidate>mark the relevant columns of data>check Use labels in left
column>OK
 
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.
Data>Consolidate>mark the relevant columns of data>check Use labels in left
column>OK

--
Regards
Roger Govier







- Show quoted text -
Click to expand...

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
 
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
Data>Consolidate>
Function>Sum
Reference>$A$1:$B$14
Use Labels in>Left 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.
 
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
Data>Consolidate>
Function>Sum
Reference>$A$1:$B$14
Use Labels in>Left 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









- Show quoted text -
Click to expand...

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
 
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 Data>Consolidate>OK.

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.
 
Back
Top