+ Reply to Thread
Results 1 to 11 of 11

sum from text

  1. #1
    Registered User
    Join Date
    10-19-2006
    Posts
    15

    sum from text

    i want to write a formula to get the sum from 1 column from text(names) from another column ex
    A B
    tom 45.00
    tom 16.00
    tom 30.00
    pete 100.00
    pete 75.00

    i want to get the sum from tom column B then get the sum from pete etc. i have about 30 names and i would like to write one formula without having to manually scroll and change the range..it seems simple but can't figure it out....thanx

  2. #2
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169
    I just joined the forum to learn the same technique ... I've actually worked around the critical issue and wanted to ask about the best techniques to sum a table with a key in "column A". I may experiment with Pivoting tables also.

    INPUT ROWS

    copy to clipboard
    Please Login or Register  to view this content.
    OUTPUT ROWS

    (I'd like to either summarize the total in another blank column on the spreadsheet, even with blank cells between them where the duplicate keys exist ... I don't need to place the key in a field, just the overall total)

    copy to clipboard
    Please Login or Register  to view this content.
    Last edited by harrywaldron; 05-24-2007 at 02:29 PM.

  3. #3
    Registered User
    Join Date
    10-19-2006
    Posts
    15

    still don't get it

    is there a formula that i can write for this?

  4. #4
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169
    Yes, and that's what's I'm asking also ...

    If this helps, one approach I've used to flag unique values is as follows:

    Finding distinct values - Make sure your table is in order by Row A

    copy to clipboard
    Please Login or Register  to view this content.
    Put this in cell C2 and paste down the # of rows, you have in the table. If will put a "1" in the 1st unique value and "0" in any duplicate keys ... It still won't total up everything, but you know where you have duplicates and you can filter by this row also

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,818
    The best and quickest way would be to create a pivot table...

    but if you want to do it with formula, it can be done... see attached sheet.

    In column D, I extract the unique names
    In column E, I get rid of the blanks and group the names up top.
    In column F, I sum based on name.

    Note, the formula in Column E is an array formula and must be confirmed with CTRL+SHIFT+ENTER after any revisions to it.
    Attached Files Attached Files
    Microsoft MVP - Excel

    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    3,982
    =SUMIF(A:A,"tom",B:B) will give you tom's total

  7. #7
    Registered User
    Join Date
    11-30-2006
    Location
    UK
    MS-Off Ver
    Microsoft Office XP
    Posts
    61
    copy to clipboard
    Please Login or Register  to view this content.
    Could come in useful to identify repetitions if its a problem.

    Jason

  8. #8
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169
    Quote Originally Posted by NBVC
    The best and quickest way would be to create a pivot table...
    Thanks NBVC and everyone who shared ... I had thought Pivoting the table was the way to go based on some research I had done earlier.

    I had actually tried this with a much more complex table than I shared. However, I'll experiment later with Pivoting on a very simple table, so I can get the views lined up properly in the new tab it creates.

  9. #9
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169

    Cool

    Just wanted to briefly follow-up, that you can teach "old dogs, new tricks"

    At home, last night, I put together a very simple table with rows A and B, and within about 5 minutes I was pivoting and summing the data in a variety of ways. It's definitely the way to go and if you experiement and document the process for later, it's far superior to a formula based approach.

    When I get a chance to breathe at work, I'll post a step-by-step approach on how to pivot a very simple table and sum up the rows. It's easy, quick, and does the job very well

  10. #10
    Forum Contributor harrywaldron's Avatar
    Join Date
    05-24-2007
    Location
    Roanoke, VA
    MS-Off Ver
    Office Professional 2010 BETA
    Posts
    169

    Lightbulb

    As an IT professional, when I was younger I used to document for others, and now I have to document for myself

    As some in our office need this functionality, I used some web development tools to capture screen by screen snapshots that hopefully folks can follow. This example using Office 2003 seems to also be similar to what I used under Office XP at home.

    Hopefully, this will help our original poster. While I've pivoted spreadsheets before using a trial-and-error approach, I now understand the process well. This Word Document (with screenshots) is in 2 parts due to 100kb size limitation for attachments and the spreadsheet referenced is also attached.
    Attached Files Attached Files
    Last edited by harrywaldron; 05-25-2007 at 11:50 AM.

  11. #11
    Valued Forum Contributor
    Join Date
    10-14-2006
    Posts
    1,131
    Quote Originally Posted by holybull
    i want to write a formula to get the sum from 1 column from text(names) from another column ex
    A B
    tom 45.00
    tom 16.00
    tom 30.00
    pete 100.00
    pete 75.00

    i want to get the sum from tom column B then get the sum from pete etc. i have about 30 names and i would like to write one formula without having to manually scroll and change the range..it seems simple but can't figure it out....thanx
    Assuming data header in row1

    Data > Filter > Advanced Filter > select Copy to another loacation > List range: select names range in column A > Copy to: select a blank cell D1 > tick the Unique records only

    In E2: =SUMIF(A:A,D2,B:B)
    copy down

+ 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