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
310 Views

Similar Articles

[PageSpeed] 49

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:

yet another resource editting thread
I need to edit resources at runtime (i.e. the string table, menus, and dialog control sizes). I would like to use the BeginUpdateResource, UpdateResource, and EndUpdateResource functions. One of the problems I have is this: If I load the menu from file with FindResource, LoadResource, LockResource, I have the binary menu data, and I can't really manipulate raw binary. If I load the menu with LoadMenu, i have a CMenu, that I can change but I don't know how to convert the final menu back to binary data that I would update with the UpdateResource command. So, how do you update? Another...

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

Inserting specific images based on product.
Hi Everyone, I'm very knew to the CRM product and was curious... For example, I am customizing a CRM interface for a company with a wide range of products. They would like to add a tab to each of their product displays showing an image of that product. I added the tab (learning how thanks to this newsgroup) but I only know how to insert a graphic using an IFRAME in the form view which in turn displays the SAME image for all product listings. Does this make sense? Basically I'm asking...is there a way to display individual images for specific products, not strictly on the fo...

assign cell content as file name
I am attempting to create a macro that will save a workbook with the file name under which I save the workbook coming from the contents of one of the cells in the workbook. Suppose that each time the workbook was altered, a specific cell (A1 on Sheet1 for instance) contained the desired file name for that workbook. It is my goal to create a macro that automatically assigns said workbook a name based on the content of that cell. Any help would be most appreciated. Something like??? dim myFileName as string myfilename = "C:\temp\" & thisworkbook.worksheets("sheet1"...

How to use MSExcel to plot Column A against Col B?
I want to plot col A (x axis) against col B ( y axis). I cant seem to do it. Can anyone here please give me step by step primer. Thanks in advance. All I get is the graph of (1,2,............n) (x axis) against the n values of either col A or B. i.e 2 graphs instead of one. (In the old Lotus this was so simple: select the column for the X axis and then select the col for the Y and press enter, and you'd get the chart) Why is it so diff in Excel? Select the entire range you want to graph such as a2:b44>insert>chart>>>> -- Don Guillett SalesAid Software dguillett1@au...

Summing Values using multiple criertia
Does anybody know a formula I could use to sum a range of values based on multiple criertia? Example: Division Type Wage Bulk Driver 200.00 Bulk Admin 400.00 General Admin 500.00 Bulk Driver 100.00 I want to sum the wages for Divison "Bulk" & Type "Driver". How can I do this??? Thanks! Jane =SUMPRODUCT((A2:A4="Bulk")*(B2:B4="Driver")*(C2:C4)) If there are lots of such totals, you may want to consider a pivot table rather than formulas. On Mon, 27 Sep 2004 20:14:27 -0700, "Jane" <anonymous@dis...

XP Home SP 2 and Web based Outlook
I used to be able to flawlessly remotely access Outlook remotely using IE and SP1. However, once I installed SP2, I can longer reply to any messages. My INBOX appears correctly and I can view messages, but once I hit the REPLY icon, the message I was viewing disappears and I am returned to my INBOX rather than being able to compose a message. I upgraded to SP2 on two different computers and the same problems exists on both machines. SP2 for Windows XP includes a built-in popup stopper for Internet Explorer. Open Internet Explorer and select Tools | Internet Options | Privacy tab. Se...

Printing
Hope you folks can help me out with a strange one. I have several worksheets formatted in exactly the same way as follows: Col A - width 4 Col B - hidden Col C - width 4 Col D - Width 108 Col E - Width 3 Col F - Width 11 Col G - Hidden Col H - Width 11 & Empty My print range should be Cols A:G (I have used page setup to set the scaling to fit 1 page wide by [blank] pages tall, thus each sheet will print as many pages as required depending on number of rows] When I have the print range set to A:G only columns A:E show on the print preview (and also on the actual print out) and when I m...

conditional sum returns not expected
Hi all. Using xl xp pro Here is conditional sum formula =SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0)) Sorry about the wrapping. Where C$2 = 7001.4500 Where C$3 = 7001.4501 Where $A5 = 54608001 Where Far_All_Data! contains data, all ref's verified However, all formulas return 0.00. As I copy down the formula Col (54608001) udates ...002, 003, 004, etc... Sometimes the expected result is 0.00 other times value should b greater than 0 Not sure how to read the formula. Does it say if, or, and? ...

initial default column width
Is there a way to configure Excel 2000 so that when I create a new Workbook or add a new Worksheet so that all the columns have a particular width instead of the default 64 pixels? TIA Create the workbook exactly the way you want it, then save it as a template with the name "Book.xlt" (no quotes) in you XLStart directory. It'll be then used as the template for new workbooks. Likewise, save a one-sheet workbook as a template, named "Sheet.xlt" for the template for Insert/Worksheet. In article <419E181F.6251D20D@nospam.net>, Bruceh <bruce@nospam.net&...

Windows and Mac have two distinctly different units for column width.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to format columns for some data I am entering in a spreadsheet and when I enter &quot;15.00&quot;, which is the required width for these columns given by my professor, I end up with a column 15 inches wide. What I would prefer is for the options to be like the default options in Windows version of Excel. In Windows version of Excel, when you hover over the lines between the columns it gives you two numbers (e.g. 8.43 (64 pixels)). These are the default numbers for column width in Windows Excel....

I cannot paste from one workbook to another. Copy works, paste do.
When I attempt to copy from one workbook and paste into another, copy appears to work but paste does not. The paste menu item is grayed out in the workbook to which I attempt to paste. Are there setting that can be changed to aloow the paste?? Check to see if either workbook and/or worksheet is protected. If so, unprotect. Then Copy/Paste should work. BTW, how are you copy?paste(ing) Tab or content? Dennis "JimmyMc" wrote: > When I attempt to copy from one workbook and paste into another, copy appears > to work but paste does not. The paste menu item is grayed out...

How to assign unique number to column duplicates?
Hi All, I need to assign a unique number to a set of duplicates all in one column in Excel 2007. so columnA will has about 9000 numbers, some of them unique, and others are duplicates of 2-4 approx. I used to conditional formatting to show which are duplicates, but need to be able to assign a unique number to each set duplicates, that will be in sequential order... e.g. ColumnA ColumnB(unique ID) 01233 0001 01233 0001 01234 - 01255 0002 01255 0002 etc.... Any ideas please? I don't know how to do programming, just form...

Excel2000: Strange behaviour for one cell in workbook
Hi The situation: There is a Excel workbook used for registering working time in firm departments, created earlier. The worksheets are protected, and I don't have the password, as author isn't working here anymore. The workbook is stored as read-only on network resource, and monthly every department is copying it to local computer and filling the table. The table's body has 2 rows data for every employee with 31 columns for month's dates, and several protected summary columns. For every date, working hours or some code string is entered into according cell. One of codes, and ...

Combine small documents into one master document
Hi, I currently have an "engagement letter" that is created for our clients to set the expectations for our business relationship (I work for attorneys). We have about 10 different paragraphs that may be inserted into the letter based on different factors (3rd party payor, if a retainer is required, etc.). So I start with the basic letter and then have a userform with checkboxes where you can select the text block options (stored in Autotext). When you click OK the various paragraphs are inserted at bookmarks. While this works, I find working with Autotext cumber...

Filter two columns with criterion applying to one or the other?
Hi, I am looking for a solution to the following filtering problem: I have two adjacent columns, so using a filter for both of them is no problem. But what I want to do and don't know how to do is this: I want to filter for values greater than x (a certain number, in my case 5000) in any of the two columns. I can filter both columns for x greater than 5000 but that filters out more than I want because there may be some cells with a value greater than 5000 in only one of the two columns. Is there a solution to this problem (using Excel alone or an add-on)? Peter Hi Peter you can use th...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

One front-end server multiple back-end servers
Hi, I was wondering if someone could help with the following scenerio and make some suggestions as to an answer. We have 3 exchange AG groups. 1 AG group has 3 exchange servers in it, each located in it's own AD domain. 1 AG group has one exchange server in it, it's in its own AD domain. Thirdly, the last AG group has 2 exchange servers in it, both are in the same AD domain. 1 of these 2 exchange servers in the final AG mentioned is a front-end server, that should be supporting all the exchange back-end servers. The problem we are facing is that when a user from any of the ...

Help: Seting the value of another cell with a formula
I know that there's a way to make a cell a particular value based on the entries of a range, or array of cells, but is it possible to do the reverse, using only one formula in a cell. Here's what I'm trying to do: What I would like to do is set one of a range of cell to have a value based on the value in A2. Example: Cell A1 has a value of 2, A2 has a value of 2007. I want A10 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2008. I want A11 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2009. I want A12 to equal...

Extract Values from a Column
I have a worksheet used to track time spent on various jobs. One column is for the JOB # while others are for descriptions, etc. I would like to have formulas or possible a macro (if necessary) to sum the time spent on different jobs. For example, say (for simplicity) that each row is equal to 1 unit of time. Then in this row I put 342 for JOB # 342. After the entire day I have worked on say 5 different jobs. I would like to have a cell that says "Total time spent on Job # 342" and then next to it a formula that would look at the column and count all the values that are equal t...

Time-based floating bar chart
I have a problem similar to "Kaixi". I have data for elapsed times of backups, and I'm trying to chart the beginning and ending time of the backups for each server. The only problem is that each server may have more than one backup job, so I need to chart the elapsed time from the earliest start time to the latest end time, with gaps shown where there is no activity. The data looks like this: server start-time end-time abc 08:00 08:30 abc 08:00 08:45 abc 09:00 09:30 def 09:00 09:10 def 0...

Re: limit numbers of connections to one server
hi there :-) my pop3 server is limited to 10 query per sec. and per ip. i have abou 16 mail-accounts on this server. the last 6 allways error with timeou or something. is there a way to limit the number of connections to one server i outlook 2002 on windows xp pro? thanks a lo - jazzy_ ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message674073.htm Set your send/receive settings to consecutive mail checks, rather than conc...

Summing with a range of number
Hey Everyone, I have a question about summing via ranges. I am trying to write macro and have hit a bump. Here is the scenario. I have a 2 columns of data (A&B). Column B i sorted in ascending order. Now I have ranges of data in column A that need to sum. Lets say in column B I need all numbers from 100 to 300 From 100 to 300 I need the totals in column A to sum at the last numbe that is less than 300 but greater than 100. I would like the sum to b in colum C for the totals of column A within the ranges of column B. hope that makes sense. If not ask for clarification or I will try an...

Report Sum/If Statement
I have a report that I am writing that will be summing footer sections with twist. I want to have the sum function state "0" if the days outstanding is greater than 2. However, if the days outstanding is less than or equal to 2 I want the report to sum the count. The formula that I am using is: =IIf([WU Day Count]>2,0,Sum([CountOfENTRY_NUM1])) This is working great if there is only a single line to add on either side (>2 or <=2) or if there are no instances of records >2 days, however if there are multiple lines where the days outstanding could be a number of days (...

Conditionally formatting a cell based on other cell values
I am trying to format a cell based upon the relationship of another cell to a reference value. For instance if the reference is 5 and the other cell is 4 I want to format the original cell green, if it is the same blue and if it is less than the reference red. Can someone help please! Please give a full explanation -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "ian" wrote: > I am trying to format a cell based upon the relationship of another cell to a > reference value. For instance if the reference is 5 and t...