Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Cells, and Sum Data
Hello,
Hoping that someone out there can help this newbie with a problem. I have a spreadsheet, where Column A shows a number which could be duplicated further down the sheet. The number of times this is duplicated may be as many as 5 times (max). What i would like, is to SUM the values in column B, where the Numbers in Column A match, and show the result in column C. See the example below:- Number 1 appears 3 times, and the sum of Column B for each of these number 1 inputs is 20. eg. Col A Col B ColC 1 10 20 1 5 20 1 5 20 2 20 30 2 10 30 3 5 5 4 42 42 etc.. Can anybody please help? Thanks in advance. Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Cells, and Sum Data
=SUMIF(A1:A100,1,B1:B100)
will sum all the B cells which have corrsponding A cell values of 1 Looking at you example: =SUMIF($A$1:$A$100,C1,$B$1:$B$100) entered in C1 and copied down the column Of course, you will need to adjust the A1:A100 range best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Mik" wrote in message oups.com... Hello, Hoping that someone out there can help this newbie with a problem. I have a spreadsheet, where Column A shows a number which could be duplicated further down the sheet. The number of times this is duplicated may be as many as 5 times (max). What i would like, is to SUM the values in column B, where the Numbers in Column A match, and show the result in column C. See the example below:- Number 1 appears 3 times, and the sum of Column B for each of these number 1 inputs is 20. eg. Col A Col B ColC 1 10 20 1 5 20 1 5 20 2 20 30 2 10 30 3 5 5 4 42 42 etc.. Can anybody please help? Thanks in advance. Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Cells, and Sum Data
On 13 Oct, 19:47, "Bernard Liengme"
wrote: =SUMIF(A1:A100,1,B1:B100) will sum all the B cells which have corrsponding A cell values of 1 Looking at you example: =SUMIF($A$1:$A$100,C1,$B$1:$B$100) entered in C1 and copied down the column Of course, you will need to adjust the A1:A100 range best wishes -- Bernard V Liengme Microsoft Excel MVPwww.stfx.ca/people/bliengme remove caps from email "Mik" wrote in message oups.com... Hello, Hoping that someone out there can help this newbie with a problem. I have a spreadsheet, where Column A shows a number which could be duplicated further down the sheet. The number of times this is duplicated may be as many as 5 times (max). What i would like, is to SUM the values in column B, where the Numbers in Column A match, and show the result in column C. See the example below:- Number 1 appears 3 times, and the sum of Column B for each of these number 1 inputs is 20. eg. Col A Col B ColC 1 10 20 1 5 20 1 5 20 2 20 30 2 10 30 3 5 5 4 42 42 etc.. Can anybody please help? Thanks in advance. Mike- Hide quoted text - - Show quoted text Bernard, I thank you very much. Just the trick.. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Match cells with cells on another workbook | Excel Worksheet Functions | |||
Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
Match Cells in Worksheets and add data from one to another | Excel Discussion (Misc queries) | |||
index,match,match on un-sorted data | Excel Worksheet Functions |