View Question
 Question
 Subject: Excel Formula Required Category: Computers > Programming Asked by: bremar-ga List Price: \$50.00 Posted: 20 Nov 2005 11:19 PST Expires: 20 Dec 2005 11:19 PST Question ID: 595486
 ```Hi, I have an Excel Spreadsheet like below: COL A COL B 1670 152 1675 256 1670 148 1672 642 1675 748 1670 364 I am looking for a way to find all the matches within in column A and calculate all the numbers corresponding in column B Desired Result COL A COL X 1670 664 1672 642 1675 1004 Thanks``` Request for Question Clarification by answerguru-ga on 20 Nov 2005 17:00 PST ```Hi there, In your example, you have a value in Column A (1670) that appears three times and pair with different numbers, but in the result only the first one is displayed in Column X. You also say you want to 'calculate all the numbers corresponding in Column B'. These statements are conflicting - could you please clarify what you want exactly? Secondly, in your 'desired result' I notice the first column is sorted - is this a requirement? Thirdly, I am assuming that if Column A and B are in one spreadsheet, Column A and X would be output to another spreadsheet. Is this correct? Also, there isn't a built in function that can do what you are asking so the answer would likely be to program a macro doing what you describe. Is this a reasonable solution for you? Based on your responses, I can let you know if the list price is reasonable for what you are asking. Thanks, answerguru-ga``` Clarification of Question by bremar-ga on 21 Nov 2005 00:39 PST ```Thanks for your quick response answerguru-ga! I am simply trying to calculate all the values in COL B, which have the same value in COL A. So the Value of (1670) appears 3 times in COL A, and has the corresponding values of (152),(148),(364). I am after the sum of these values which is 664. Sorting is not a requirement. In regards to my desired result, the placement of the New COL A and COL X, can be on the same spreadsheet or a different workbook. This is not important to me. Macro or formula is fine. Thanks answerguru-ga```
 Subject: Re: Excel Formula Required Answered By: answerguru-ga on 21 Nov 2005 11:19 PST
 ```Hi bremar-ga, Based on your original sample and the clarification you have provided, I have written a function in VBA which will allow you to sum the values in column B which all have the same value in column A. The text for this function is listed below: ' Function starting Function SumAssociatedValue(ByVal ref As Integer, ByVal range As range) As Integer Dim sum As Integer sum = 0 Dim cell As range For Each cell In range If cell.Column = 1 And cell.Value = ref Then sum = sum + range.Cells(cell.Row, 2) End If Next cell SumAssociatedValue = sum End Function ' Function Over In order to use the function, you will first need to copy it into a module within the file you are planning to use it. The steps to do this are as follows: 1. Open your spreadsheet and go to Tools > Macro > Macros 2. Select Edit to display the Visual Basic editor 3. On the left panel, double click on a module to open it (by default there should be a module called Module1) 4. A text window will be displayed (empty unless you have anything else in that module) 5. Paste the function text into the module 6. Select File > Save to save the changes You can call the function as follows: =SumAssociatedValue(lookupValue,range) lookupValue: This is the value in Column A for which you want the sum of the associated values range: This must be a two-column range (specifically columns A and B as in your example) with as many rows as you like. So, using your original example the following call: =SumAssociatedValue(1670,A1:B6) will yield a result of 664. If you have any problems or questions regarding the information above, please do let me know by requesting clarification prior to rating the answer. Cheers! answerguru-ga``` Request for Answer Clarification by bremar-ga on 21 Nov 2005 13:42 PST ```Thanks answerguru-ga Having problems with running the Macro. I followed the following steps: Open your spreadsheet and go to Tools > Macro > Macros 2. Select Edit to display the Visual Basic editor 3. On the left panel, double click on a module to open it (by default there should be a module called Module1) 4. A text window will be displayed (empty unless you have anything else in that module) 5. Paste the function text into the module 6. Select File > Save to save the changes I called the macro 'TESTS' and saved it, (As it wouldn't let me call it =SumAssociatedValue(lookupValue,range) But it does not appear in my MACRO lists to run it? Sorry, I am not real experienced with macros Brett``` Clarification of Answer by answerguru-ga on 21 Nov 2005 14:56 PST ```Hi there, This is actually a function, not a macro. By saving it in the module (as long as your module is in the same file as your workbook) you will be able to access it just like any other worksheet function that is built into Excel. I noticed that mdw811-ga has commented with an alternative approach, however he/she is not an official Google Answers Researcher and can only comment on questions (not actually answer them). You can spot a researcher by the hyperlink attached to our names on posts. If there is anything else I can assist with in getting the function properly situated in your workbook please let me know and I'd be happy to help. Thanks, answerguru-ga``` Clarification of Answer by answerguru-ga on 21 Nov 2005 15:06 PST ```I looked at your steps again and I wanted to ensure you understood the process. Once you have pasted the function into the Visual Basic Editor, you just need to save within the editor (File > Save myFile.xls). What I meant by 'calling' the function was to actually place this text: =SumAssociatedValue(lookupValue,range) into the cell in your worksheet where you want the result to display. So, the text you would actually place in your worksheet cell would look like this: =SumAssociatedValue(1670,A1:B6) Something that you can call from within a cell in Excel is known as a function, whereas a custom procedure which is invoked through a menu command is called a macro. Hope that clears things up! Cheers, answerguru-ga```
 Subject: Re: Excel Formula Required From: mdw811-ga on 21 Nov 2005 13:29 PST
 ```An Excel pivot table would do what you're asking. Highlight your table Select Data:Pivot Table Select Next Verify the range Select Next Select Layout Drag Col A into the row area Drag Col B into the data area double click Col B to change the data to SUM OK Select the location for the output table Click finish```
 Subject: Re: Excel Formula Required From: bremar-ga on 21 Nov 2005 13:54 PST
 ```Hi mdw811-ga Nice and simple, worked great....thanks How do I confirm you answered the question through Google.```
 Subject: Re: Excel Formula Required From: mdw811-ga on 22 Nov 2005 12:30 PST
 ```Mine was only a 'comment' I'm not one of the paid researchers. I just thought the answer was a little more than what was needed. Good luck```
 Subject: Re: Excel Formula Required From: bremar-ga on 22 Nov 2005 23:49 PST
 `Many thanks mdw811-ga!!`
 Subject: Re: Excel Formula Required From: manuka-ga on 23 Nov 2005 00:39 PST
 ```Here's *another* fairly simple way to get your desired result: Select column A and go to Data > Filter > Advanced Filter. Click on 'Copy to another location' and 'Unique records only', and enter D1 in the Copy to: box. (I'm assuming you have row 1 for headings and the data starts at row 2). This will give you the values from A with the duplicates removed. Then, in cell E2, enter =sumif(A:A,D2,B:B), and fill this formula down to the end of the data. I personally prefer this method because I like SUMIF and I don't like PivotTables. Your mileage may vary a bit. ;-) SUMIF(source, criterion, target) adds together all cells in the target range that correspond to cells in the source range matching the given criterion. In this case we're comparing all the cells in column A against the given value in D2 (or D3 or ...) and for the ones that match, adding together the corresponding cells in column B. It's quite a nice function - there's also a COUNTIF(source, criterion) function if you just need to know how many cells match. The beauty of COUNTIF/SUMIF is that the criterion doesn't have to be a cell - it can also be a string expression, and you can use this to good effect if you need a complicated data-dependent match. Particularly useful for inequalities - you can do something like "=SUMIF(A:A, ">"&ROUND(B2*0.25+C2, 2), D:D)", for instance. This is getting pretty far off the topic, but SUMIF also works much faster than doing a multi-criterion match using SUM(IF(...)). One technique I've used quite often when I need to match on multiple conditions is to add a Key column to one worksheet, containing all the key data concatenated together (delimited by something like ||). Then I can use COUNTIF, SUMIF and VLOOKUP on the other sheet to find the appropriate data row(s) (e.g. =COUNTIF(Sheet1!Keys, A2&"||"&B2)). If I need to match both ways I add a Key column to both worksheets, and then I can match directly on the key columns.```
 Subject: Re: Excel Formula Required From: gjsfus-ga on 25 Nov 2005 12:08 PST
 ```bremar-ga All the above are either wrong or too over done. The solution to get your desire result is a simple 5 click process. And that applies to 6 pair of number or 10,000 pairs of numbers. Click: Select, Data, then select, Pivot Table Select, Multiple Consolidated Range Select, Create a single page field for me, Next Highlist the range A1:B6 (or what ever long), next Select, Existing Worksheet, finish Good luck gjsfus```
 Subject: Re: Excel Formula Required From: bremar-ga on 27 Nov 2005 03:44 PST
 ```Thanks gjsfus! Nice and simple```