+ Reply to Thread
Results 1 to 5 of 5

Calculate totals for each onto another worksheet

  1. #1
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Calculate totals for each onto another worksheet

    hi everyone,

    I have a worksheet ("Dashboard")with names in column H (range is h3 down ) and their $ values in Column F (F3 down).

    I need to somehow find the total value for each of the names (most of the names appear more than once...) and put them into another worksheet called 'Totals' starting at Column C, row 3.



    So in effect if the name Sarah appears 10 times in column H there would be values for each entry in the adjacent column F.

    So the results on the "Totals" worksheet would have something like:

    C B
    SARAH 10,000 (10,000 being the cumulative total)
    CORN 20,000


    I'm not sure if a macro or formula is best for this..

    Thanks in advance,
    Last edited by NBVC; 05-06-2011 at 03:52 PM.

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Calculate totals for each onto another worksheet

    Hi Sarah,

    You should be able to do this with the SUMIF function.

    A formula in your example might look something like this:

    Please Login or Register  to view this content.
    Of course this would depend on where you have the name "Sarah" on your Totals worksheet. (I've assumed it is in B3).

    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Calculate totals for each onto another worksheet

    Hi Connex,

    Unforntunatly the names wihtin the dashboard H column could be anything, so I wont be able to predict what could be entered into H3,H4,H5,- H200.

    Ideally would need to have the unique names that are entered in the H column to be written to the 'Totals' worksheet and then the dollar values calculated.

    Ive been trying to workout a way to do ths using formulas this morning but more or less just gave up after 3 hours of failing.

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Calculate totals for each onto another worksheet

    Have you tried using:

    "Data > Filter > Advanced Filter..."

    and asking for unique values?

  5. #5
    Forum Contributor
    Join Date
    12-02-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    209

    Re: Calculate totals for each onto another worksheet

    Hi,

    I dont want to use a filter as I dont want the intended user(s) to fiddle around with options.

    What that said I was able to clean up the data and the layout and created 2 pivot tables that work fine rather than coding a macro.

    I was going to create a macro that loop through a range for all interations of a name and then offset 1 column to get the value & then add it.. but the pivot does it

    Thanks for your help

    S

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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