

Subject:
Excel Formula Required
Category: Computers > Programming Asked by: bremarga 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  
 


Subject:
Re: Excel Formula Required
Answered By: answerguruga on 21 Nov 2005 11:19 PST 
Hi bremarga, 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 twocolumn 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! answerguruga  
 
 


Subject:
Re: Excel Formula Required
From: mdw811ga 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: bremarga on 21 Nov 2005 13:54 PST 
Hi mdw811ga Nice and simple, worked great....thanks How do I confirm you answered the question through Google. 
Subject:
Re: Excel Formula Required
From: mdw811ga 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: bremarga on 22 Nov 2005 23:49 PST 
Many thanks mdw811ga!! 
Subject:
Re: Excel Formula Required
From: manukaga 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 datadependent 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 multicriterion 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: gjsfusga on 25 Nov 2005 12:08 PST 
bremarga 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: bremarga on 27 Nov 2005 03:44 PST 
Thanks gjsfus! Nice and simple 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 