Summing the contents of one column based on the contents of another?

Hi again everyone,

Ok, after I got several speedy and very helpful responses to my last
query, I figured I could do worse than asking for help here again!
Maybe when I learn some more I can help dish out advice instead of just
asking questions!

Here's my problem:

I have a large spreadsheet with data in columns A and B. Column A
contains lots of strings. Column B contains numbers. eg:

Column A          Column B

Piano                   4
Saxophone          5
Clarinet               12
Piano                   2
Piano                   6
Clarinet               1

Ok, what I want to do is get totals for each item. So for the example
above, I'd like to end up with a spreadsheet looking like this:

Column A          Column B

Piano                   12
Saxophone          5
Clarinet               13

So that duplicates in column A are eliminated and there's just one
entry for each unique string with the total number in column B.

I've been playing around with VBA but not really getting anywhere -
would appreciate some help if anyone's got a little time to spare.

Thanks again,

John.


---
Message posted from http://www.ExcelForum.com/

0
1/6/2004 4:33:33 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
347 Views

Similar Articles

[PageSpeed] 31

Hiya Red,

you could use the SUMIF worksheet function as follows: -


=SUMIF(A1:A7,"Piano",B1:B7)

This could be typed in any cell (other than one in column A or B).  You
will need to change the references to those on your worksheet according
to the following criteria: (available in help)

SUMIF(range,criteria,sum_range)

Range   is the range of cells you want evaluated.

Criteria   is the criteria in the form of a number, expression, or text
that defines which cells will be added. For example, criteria can be
expressed as 32, "32", ">32", "apples".

Sum_range   are the actual cells to sum. The cells in sum_range are
summed only if their corresponding cells in range match the criteria.
If sum_range is omitted, the cells in range are summed.

Hope that helps!


---
Message posted from http://www.ExcelForum.com/

0
1/6/2004 4:49:16 PM
John,

You could make a pivot table.

You could use a SUMIF (would require you to keep the current data)
Example Sheet1 A1:B10 has your data.
Summary in Sheet 2 A1:B3

in Sheet 2 B1:
=SUMIF(Sheet1!A$1:A$10,A1,Sheet1!B$1:B$10)

drag down

Dan E


"redmist >" <<redmist.zmcdv@excelforum-nospam.com> wrote in message news:redmist.zmcdv@excelforum-nospam.com...
> Hi again everyone,
>
> Ok, after I got several speedy and very helpful responses to my last
> query, I figured I could do worse than asking for help here again!
> Maybe when I learn some more I can help dish out advice instead of just
> asking questions!
>
> Here's my problem:
>
> I have a large spreadsheet with data in columns A and B. Column A
> contains lots of strings. Column B contains numbers. eg:
>
> Column A          Column B
>
> Piano                   4
> Saxophone          5
> Clarinet               12
> Piano                   2
> Piano                   6
> Clarinet               1
>
> Ok, what I want to do is get totals for each item. So for the example
> above, I'd like to end up with a spreadsheet looking like this:
>
> Column A          Column B
>
> Piano                   12
> Saxophone          5
> Clarinet               13
>
> So that duplicates in column A are eliminated and there's just one
> entry for each unique string with the total number in column B.
>
> I've been playing around with VBA but not really getting anywhere -
> would appreciate some help if anyone's got a little time to spare.
>
> Thanks again,
>
> John.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
1/6/2004 4:53:44 PM
Ok, I can see how that works, but I have a couple of thousand rows i
this spreadsheet - is there any way of replacing "Piano" with som
expression that will grab the contents of cell A in the relevan
column? Don't fancy typing out each of the thousand-odd strings

--
Message posted from http://www.ExcelForum.com

0
1/6/2004 4:55:29 PM
Data|Subtotal will work if you sort the data first.

VBA should also accomplish this pretty quickly if you 
know how to program.

Mike.


>-----Original Message-----
>Hi again everyone,
>
>Ok, after I got several speedy and very helpful 
responses to my last
>query, I figured I could do worse than asking for help 
here again!
>Maybe when I learn some more I can help dish out advice 
instead of just
>asking questions!
>
>Here's my problem:
>
>I have a large spreadsheet with data in columns A and B. 
Column A
>contains lots of strings. Column B contains numbers. eg:
>
>Column A          Column B
>
>Piano                   4
>Saxophone          5
>Clarinet               12
>Piano                   2
>Piano                   6
>Clarinet               1
>
>Ok, what I want to do is get totals for each item. So 
for the example
>above, I'd like to end up with a spreadsheet looking 
like this:
>
>Column A          Column B
>
>Piano                   12
>Saxophone          5
>Clarinet               13
>
>So that duplicates in column A are eliminated and 
there's just one
>entry for each unique string with the total number in 
column B.
>
>I've been playing around with VBA but not really getting 
anywhere -
>would appreciate some help if anyone's got a little time 
to spare.
>
>Thanks again,
>
>John.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
1/6/2004 5:13:24 PM
Thanks for all the replies everyone!

I ended up using the "subtotal" thingy as it was the simplest way an
did what I needed. I always look for the more complex solution...

--
Message posted from http://www.ExcelForum.com

0
1/6/2004 5:58:39 PM
Reply:

Similar Artilces:

Duplicate Messages from one user
Anyone know of any common issues with why when one user sends mail that the recipients would get two of the same mails? The mails have different message thread ID's and this happens randomly. I've checked the Exchange settings to see if certain mail for certain people is set to forward and things look fine. Just not sure of why this one user's mail would be hitting people's inbox multiple times. Thanks, Dan What e-mail client, and which protocol (MAPI, SMTP) are they using to send? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!&qu...

Transfering data from one spreadsheet to another.
I have a spreadsheet that is created by a program I use at work. I run this report weekly and there are hundreds of loads each week. The spreadsheet has the info I need but not in the right format. I need to know if there is a way to pull this info into another spreadsheet in the format I am looking for without a bunch of copying and pasteing. He is an example of what the report looks like Load# Destination Charge Desc. Charges Total 12345 Columbus, OH Fuel $100 $300 12345...

Dating every third column?
I'm not a regular poster here, so if I'm asking a question that's very common, please forgive me. I did do a google groups search and found out that what I would like to do--label multiple columns with one label--isn't possible in Excel 97. But can I date every -third- column without doing it manually? What I have is a sheet with a column labeled, say, "01/02/04", and then two unlabeled columns (one applies to that date; the other is a spacer) and then a column labeled "01/03/04", then two blanks, etc. What I've been doing is revamping an old workboo...

How to move highlighted cell to a new column
I got a huge database of fruit related terms such as : apple apple pie apple ipod apple computer banana milkshake kiwi kiwi map kiwi maple syrup orange juice orange mobile .... etc. I want to filter out all the non edibles such as ipod, computer and mobile. I used conditional formatting to highlight all the non-edibles at the moment, and terms such as ipod, computer, mobile, map and maple syrup were highlighted. However the kiwi maple syrup is edible, and i dont want it to be highlighted. But you cant manually check every cell if you got like 10000 rows (just an example). Is there a way ...

Open with Link Criteria and Columns in Datasheet
I have two questions. First, how do you order columns in a datasheet view? I tried to order them in the query and on the form, but neither seem to change the column order. Next, I'm trying to open a form "frm_CSearch" where the data in the field "Search" is LIKE whatever is keyed in on the form "MainSearchText". Below is what I thought would work: Dim stDocName As String Dim stLinkCriteria As String stDocName = "frm_CSearch" stLinkCriteria = "[Search] like *" & Me!MainSearchText & "*&q...

Divide one row over other row I dont wont to divide one number ov.
Dear All I need to divide one row over other row, is there any help i dont wont to divide one value over other but i need function to do for the complete row or column. ...

Excel
I have been entering data from surveys into an Excel spread sheet. Each respondent may have up to twelve types of problems that they will have written something about. I have created a separate column for each row.This is what it looks like (if you get my drift). -------- ------- ----------------- ----------------------- |Id No: |Name: |Problem 1(Engine)|Problem 2(Transmission)| -------- ------- ----------------- ----------------------- |1000 |John |Too noisy |Gears hard to change | -------- ------- ----------------- ----------------------- (with 12 columns for the pr...

sum of time
Hi all I have a column with HH:nn data I want excel to sum the values - is it possible ? e.g.: 12:40 20:10 ------------- 32:50 TIA Guy Just sum as usual and format the results cell as [h]:mm -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Guy Cohen" <noemail@please.com> wrote in message news:#L1NYsKCHHA.4024@TK2MSFTNGP04.phx.gbl... > Hi all > I have a column with HH:nn data > I want excel to sum the values - is it possible ? > e.g.: > 12:40 > 20:10 > ------------- > 32:50 > > TIA > Guy > > ...

Too many subforms in one form?
Hi, I think I've got my tables down... Now, here's my situation. I have t_PondSampleData with a PK of PondSampleDataID that hinges on pond name (PondID) and date. This table contains general data (e.g., maximum pond depth, sampler, aerators on or off). Then I have 5 tables of more specific data, all linked to the pond sample data table by way of foreign keys. My question is, what is the best way to create data entry forms? I need for the PondSampleDataID to carry from the main table forth to these more specific tables and I see that creating a subform within the...

Macro
Hi, I have this database from column C to T and row 4 onwards. What I hope to achieve is to copy the entire row from C to T to another sheet (similarly from Column C to T and row 4 onwards) if the cell for that row in Column L is "Lost Case". At the same time, the selected rows to be copied in the original sheet should be deleted after being copied. Another consideration is that new data will be added to the original sheet from time to time and thus the database might expand or shrink (after being copied and delete, and the addition or new data). Thus, when the macr...

Spreadsheet Printing and Displaying on one pc in Blue Ink
One of our users is still having trouble with an Excel spreadsheet. It is printing in blue ink on his pc and black on everyone elses. I modified the spreadsheet cell colors to black instead of automatic and it still prints blue on his. I had him check his palette to make sure black was highlighted as the default color and he said it was. He also told me that he is able to print in black ink using other applications on his PC, so he isn't out of black ink. Any suggestions? Thanks! Ken K. - 2191 -- akkrug After discussing with HP Tech Support, We just selected print to graysca...

How to transfer email hyperlink to another cell
I have an email hyperlink in cell A1. I want the same email address to automatically go into C1 and have it be a clickable hyperlink just link A1 is. Obvioulsy, I can formulate C1 as =A1 but the problem is that it is not a clickable hyperlink. Paul PS - I am not familiar with macros. How about: =hyperlink(a1) or =hyperlink("mailto:" & a1) depending on what's in A1. Peepster wrote: > > I have an email hyperlink in cell A1. I want the same email address to > automatically go into C1 and have it be a clickable hyperlink just link A1 is. > > Obvioulsy,...

Data From One Sheet to Another
I'm trying to set up my home based business ans keep track of stock et. I've set up a workbook with several worksheets. The first shows all the product purchased, columns for:- Date: Invoice #: Quantity: Product Descript: A/Ship (mandatory monthly purchase): Personal: Retail: ReSale: Promo: Shipping: Other: Cost: Each of the other sheets id dedicated to one of the catagories, i.e. A/Ship, Retail, Promo etc. To save entering each invoice a couple of times, how can I get Excell to populate the additional sheets when I enter the data in the first? Hi Richard There are many w...

Victorinox Swiss Army Base Camp Mens Watch V.25331
Victorinox Swiss Army Base Camp Mens Watch V.25331 Discount Watches Site : http://www.watchespurchase.com/ Victorinox Swiss Army Base Camp Mens Watch V.25331 Link : http://www.watchespurchase.com/victorinox-base-camp-mens-watch-V25331.html Victorinox Swiss Army Base Camp Mens Watch V.25331 Information : Brand : Victorinox Swiss Army Watches ( http://www.watchespurchase.com/Victorinox-Swiss-Army.html ) Gender : Mens Code : victorinox-base-camp-mens-watch-V25331 Item Variations : SWISS ARMY 24331 V24331, Victorinox-V24331 Movement : Quartz Bezel :...

How to call a function from another workbook
Dear sir, how can i call a function or procedure that contained in another workbook ? thanks Joe Hi When you want to use an UDF or procedure in several workbooks, then save it into a module of Personal Macro Workbook (Personal.xls, it's created automatically, when you select Personal Macro Workbook as destination to save a newly-created macro, and later is loaded automatically whenever you start Excel). -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "Joe" <Joe@discussions.microsoft.com> wrote in message news:AF3EABAE-6751-4917-88F1-1A5...

Another HARD VLOOKUP Problem, can it be done ?
A hard problem with VLOOKUP ! Have serveral lines with Names/Adresses/Zip and a Cell with Codes of my Family in Excel 2003. I want to search it with VLOOKUP and one Cell of the found data must than be viewed in a Horizontal way. Example: VLOOKUP finds seven adresses with different codes T 3 T 2 G 4 E 7 W 4 The question is how do i get this data Horizontal like this : T 3 - T 2 - G 4 - E 7 - W 4 ( - = next Cell) How do i do that ? Is "=TRANSPOSE" an option and will it work with "VLOOKUP" ? Anybody ? > Example: VLOOKUP finds seven adresses...

copy row&column containing max value
I am trying to record a macro that finds the maximum value in a gri file. Basically, I have this huge worksheet, with lots and lots of column and rows (maybe 1000x100). I have more than a thousand of these hug worksheets, so I need to create a macro! Steps for the macro: - I need to find the max value in the worksheet (cell location change per worksheet, obviously). - I need to take the row that the max value cell is located. - Take this row and set it as the y-values for a chart, and se 1,2,3...n for x-values for the chart - Repeat steps above to find the column containing the same max...

Changing entity view columns
Gidday all, My problem: Im trying to change the view of the 'products' page so that it also displays the list price, but when I add it in, I also wanted to move it over next to the item ID column. I first added the list price column and went into products to check it our (I set it up for the 'active product view') but it doesn't appear there. Then when I try to go back to the entites view and adjust the arrangement of the columns, it tell me to select one of them, which I cannot do.. Note, there is a warning message below the list (in the edit view) which states: Im...

Copy from one calendar to another in Outlook 2003?
Is there a way to copy all events from one calendar to another in Outlook 2003? I have four different calendars in Outlook and would like to use a different one for default than what I've used in the past. I tried to export from one, exporting as a csv file, and re importing it into the other. It doesn't work. What's prompting this is I started to use Outlook Connector and it seems that the folder that Outlook Connector wants to share on the web is my MSN calendar. Any ideas? Thanks, Sam The default Calendar is the one that belongs to the store where mail is being delivere...

How to have one year value added to another cell
I have a cell where I date foramtted fill in the date. How to have on another cell the date + one year automatically filled? So, A1 02-04-06 auto: B1 02-04-07 Bart Excel 2003 =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Bob Umlas Excel MVP "AA Arens" <bartvandongen@gmail.com> wrote in message news:1164983094.802613.212520@l12g2000cwl.googlegroups.com... > I have a cell where I date foramtted fill in the date. > How to have on another cell the date + one year automatically filled? > > So, > > A1 02-04-06 > > auto: > B1 02-04-07 > > > Bart >...

recovery of DB to another servers SG?
Is it possible to recover a mailbox database to another Exchange servers recovery storage group from the same org? I highly doubt it, but my boss wants me to confirm. Thanks. Yes, as long as they are in the same administrative group. -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburner.com/winzenz (RSS Feed) "BigHaig" <BigHAig@aol.com> wrote in message news:tufYf.314$Fy2.264@newsread3.news.pas.earthlink.net... > Is it possible to recover a mailbox database to another Exchange servers > recovery storage grou...

how do you keep a column in date form
i'm making a spreedsheet in office excel, in column A I have a list of dates. I would type in 4/5 for an date and excel would automatic change the date to 4-Apr. I like this way, but when I got to cell 26, the change will not happen. I type in 4/5 and I get just the number 1 in the cell, for the rest of the cells down, can someone help me. Copy the cell that has the format you like and paste format it to the cells that you want to have the date format. "s2m2" <s2m2@discussions.microsoft.com> wrote in message news:F350F90F-24B9-4B79-BE39-20AB0414015D@microsoft.com......

Move messages based on subject
I have an Exchange 2003 mail server running. I also have an e-mail filtering device that sends possible spam messages to the end user with a subject of [LIKELY SPAM]. I would like to setup a ?rule? at the exchange server level that any messages with a subject line of [LIKELY SPAM] be forwarded to the mail administrator. How can I set this up? Thank you in advance for your assistance. Rene On Fri, 13 Jan 2006 11:53:03 -0800, "Rene Ditton" <Rene Ditton@discussions.microsoft.com> wrote: >I have an Exchange 2003 mail server running. I also have an e-mail filtering &...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

Find first non-blank or non-zero in a column of data
I have my data in, say row A - specifically A1:A50 - and want my calculation to look from the bottom of A50 to the top of the row (A1) and return the first non-blank OR the first non-zero within that data. What is the best way to do this? E.g. A1 = 4 A2 = 3 A3 = 0 A4 = blank cell A5 = blank cell Answer would be 3 as it looks from the bottom. Many thanks, Jim Jim, If it is just numbers, use =INDEX(A:A,MATCH(9.99999999999999E307,A:A)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "DerbyJim" <DerbyJim@discussions.microsoft.com> wrote ...