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