sum from text

[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

[harrywaldron]

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

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)

Please Login or Register  to view this content.

[holybull]

is there a formula that i can write for this?

[harrywaldron]

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

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

[NBVC]

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.

[mikerickson]

=SUMIF(A:A,"tom",B:B) will give you tom's total

[Json]

Please Login or Register  to view this content.

Could come in useful to identify repetitions if its a problem.

[harrywaldron]

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.

[harrywaldron]

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

[harrywaldron]

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.

[Teethless mama]

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