Help with searching and then adding numbers

[Verg159]

Hello,

I am sure this is a real easy formula, i just know the basics and need to make this easy on myself.

So say in Column A i have a bunch of numbers that are sorted. On 3 of the rows are the same number on column B i have different numbers, i want to add these numbers together.

For instance on column A i have 123456 on row 1,2 and 3. Then i have 789 on column 4,5 and 6. Now on Column B i have the number 5 on row 1, 6 on row 2 and 7 on row 3. I want to add 5+6+7 together to give me the total of 18 on column C.

Column A Column B Column C

123456 5
123456 6
123456 7 How do I get this to total the sum of (5+6+7)?
789 13
789 17
789 25 Sum of 13+17+25 ?

Does this make sense? Thank you in advance

 

[shemayisroel]

Hi & Welcome,

To gte the below result in column C, manually type the formula in C3, the highlight C!:C3 and simply fill down. It will yield your desiered result

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">123456</TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">123456</TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">123456</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">789</TD><TD style="TEXT-ALIGN: right">13</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">789</TD><TD style="TEXT-ALIGN: right">17</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: right">789</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">55</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C3</TD><TD>=SUM(B1:B3)</TD></TR><TR><TD>C6</TD><TD>=SUM(B4:B6)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Verg159]

Thank you for the quick response, but i don't think that is what i am looking for. I have this for thousands of rows and some might have 1 row and others might have 10 rows with the same numbers. Instead of me manually going down the list, i am looking for something i could write and then drag down the column.

 

[shemayisroel]

ok, you'll need to define the criteria/business rules of when to add up what under what circumstances...

From there a solution perhaps can be drawn up.

Best way to do it would to dispay some sample data with the results and expalin how you came to those results.

 

[Verg159]

I cant figure out how to upload the image to this site, so if you dont mind going here..... I uploaded a .jpg of the actual file i am working on.

http://img29.imageshack.us/i/224201140556pm.jpg/

 

[shemayisroel]

Ok so I've looked at the file, what criteria are you using to sometimes sum 3 rows, sometimes 1 row etc?

Port number change?

 

[Verg159]

Just the part number.. Every part number is a different part but they can be used in different locations. For instance 20583848 is used in 3 locations and 20721859 is used in 2 locations. I need to know the total sum of all locations for each part number.

 

[shemayisroel]

ok, the below is a quick way of doing it

Formula in C2 copied down

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 103px"><COL style="WIDTH: 105px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">PART NUMBER</TD><TD style="FONT-WEIGHT: bold">NEXT 12 months</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">555</TD><TD style="TEXT-ALIGN: right">1665</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">333</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">777</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">1221</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">222</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">888</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">777</TD><TD style="TEXT-ALIGN: right">1110</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">333</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">444</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">111</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">222</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">888</TD><TD style="TEXT-ALIGN: right">888</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">999</TD><TD style="TEXT-ALIGN: right">1221</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">222</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=IF(A1=A2,"",SUMIF($A$2:$A$15,A2,$B$2:$B$15))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Verg159]

Wow, it took me longer to figure out how to type this out than it took for you to figure it out... Much appreciated, works perfect!

 

[shemayisroel]

Wow, it took me longer to figure out how to type this out than it took for you to figure it out... Much appreciated, works perfect!

Your welcome, glad you got what your looking for.