Yet another Excel thread - Subtotals

Factual Questions
1

Using the subtotal function in Excel is rather straightforward and easy. However, the default formatting after using the function isn’t what I want. For example, let’s say that column A contains a list of dates, companies and their income for that date - something like this: (The list has previously been sorted so that the companies are grouped together.)

Col A______Col B__________Col C
1/2/14___Company A______532.12
1/6/14___Company A______ 31.58
1/10/14__Company B______123.00
1/15/14__Company C______ 10.52
1/20/22__Company C______100.31

(Sorry for using underlines - couldn’t preserve the columnar formatting by just using spaces.)

Now if the Subtotal routine has been run, calculating the subtotals for each company, Excel will correctly calculate the total amount of income for each company and insert this into a line after each group of companies. For Company A this list will have the statement “Total Company A”, and then in column C, the total amount - in this case, 563.70. This will continue for the entire list, showing the total amount each company earned and inserting it after each group of the same company.

My problem with this is that, to me, having these subtotals placed in the same column as the individual incomes (C, in this case), results in a big list with all the numbers, both individual and subtotals, right under each other, and is somewhat confusing to read and/or review.

Like this:
Col A______Col B__________Col C
1/2/14___Company A______532.12
1/6/14___Company A______ 31.58
Total Company A____563.70
1/10/14__Company B 123.00
Total Company B____123.00
1/15/14__Company C 10.52
1/20/22__Company C______ 100.31
______Total Company C____110.83

As you can see, this format is not clear or easily read

What I want Excel to do is to not put the subtotals into column C, but instead leave the cell in column C blank, and place the subtotal instead into Column D. So the result would look like this:

Col A______Col B__________Col C______Col D
1/2/14___Company A______532.12
1/6/14___Company A______ 31.58
Total Company A______________563.70
1/10/14__Company B 123.00
Total Company B______________123.00
1/15/14__Company C 10.52
1/20/22__Company C______ 100.31
______Total Company C______________110.83

To me, this format is much clearer than the previous one.

So far, nothing I’ve tried (including reading the help files), will generate this formatting. If any Excel experts out there can help, I’d be most appreciative.

2

Missed the edit window. The second sentence on the first paragraph should have been:

“For example, let’s say that column A contains a list of dates, Column B the companies and Column C, their income for that date - something like this:”

3

You can do something close with a PivotTable. Example here.

The subtotals live in their own columns, but that same column contains redundant individual values… so close, but not exactly what you want.

4

The subtotal function takes a reference, so you can put this in D3:

=subtotal(9,c1:c2)

unless I’m missing something obvious about what you are attempting.

5

I don’t think the subtotal function (as opposed to the wizard/command) can group results by company name. Using that would require a lot of manual definitions of ranges, such as subtotal(c1:c4) for company A, c5:c19 for company B, etc.

6

Never mind the above - I guess you’re working with Excel 2010, which I’m not familiar with.

Carry on without me.

7

Forgot to mention the I still use Excel 2003. Please don’t tell me to upgrade.

8

That pivot table might be OK - the output was much more readable. Now I’ve got to read up on pivot tables, not having used one before. Might learn something useful!
Thanks

9

Filter so only subtotal rows are showing.
In the first subtotal row, format column c cell to text color is white. In column d enter formula “=offset(xy,0,-1)” where xy refers to the cell the formula is in.
Copy those two cells
Select only visible cells (I think its alt-:wink:
Then paste the tow copied cells

10

Not sure if this helps you at all, but do you know on the left side of the table there is a 1, 2, 3.
If you click the 2 you get the subtotals without the individual values.
Maybe that’s all you need, maybe you can copy them and paste (special, values) 1 column to the right or wherever you want it.