+ Reply to Thread
Results 1 to 5 of 5

Subtotal each cell in a column

  1. #1
    Forum Contributor
    Join Date
    08-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    132

    Subtotal each cell in a column

    I have a sheet with about 3000 lines that looks something like this:

    A...............B..................C
    1234........$42.................Subtotal of all values in group "1234"
    1839........$58.................Subtotal of all values in group "1839"
    1837........$15.................etc
    1234........$11.................etc
    1592........$19.................etc

    The numbers in column A refer to a specific "group" number - anything in this group I want to subtotal into column C.

    I'm not sure how to do this though. I've looked at the =SUBTOTAL function, but I'm not sure exactly what to do to make what I'm trying to do work.

    I wrote a For Each loop to go through each cell and add values, but that took like 4 hours to run - so.. that's not the way to go lol.

    Any ideas?

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Subtotal each cell in a column

    Try this, assuming your data starts in A1 & B1 and you want the subtotals in C1 and downward:

    Put this in C1, then you can fill down: =SUMIF(A:A,A1,B:B)

    - Moo

  3. #3
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Subtotal each cell in a column

    I would use the Sum function (sheet side) and then double click the copy handle in the cell. It should add the formula to each cell for all 3000 rows really quickly.


    EDIT: Moo's got you!
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  4. #4
    Forum Contributor
    Join Date
    08-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    132

    Re: Subtotal each cell in a column

    Bee - You - Tee.

    This worked perfectly. Thanks!

  5. #5
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Subtotal each cell in a column

    Of course, as a better option, you could also use a Pivot Table.

    Say your data is in A2:B3000, and you have headers in A1 and B1. Select cells A1:B3000, click on Insert 'Manual' Pivot Table toolbar button. Select both headers in the 'Field Name' box, then just add the Group column label to the 'Row Labels' box, and add the dollar values column header to the 'Values' box. And there you have it. Nice and easy, and you don't need to have the same formula copied down 3000 cells.

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Marco for pivot table like fuctions. Subtotal column C, based on column B criteria.
    By RobertOHare in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2013, 02:57 PM
  2. Replies: 8
    Last Post: 07-31-2012, 09:41 AM
  3. Subtotal - Delete zero Subtotal and prior rows that calculate to that zero Subtotal
    By Whatsherface in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2012, 08:37 PM
  4. Replies: 1
    Last Post: 12-23-2010, 03:12 AM
  5. Find Last Filled Cell, Negate it, determine another cell and subtotal a column
    By kerl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-30-2007, 06:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1