Totalling Data Based on Criteria

[jhedges]

I trying to find away to do the following with my data (sample below):

1. In the Team Name(column D) I want to find a total from Workouts(column E) where Column D has the same team name. So, for example Team Name (4Fun) has a total of 18 workouts. I can do a =sum(E2:E5); however, I have 300 rows of data and thought there might be an easier way. I tried vlookup; however, that just gives you the first row in the list...Could someone help me with this issue?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"><COL style="WIDTH: 205px"><COL style="WIDTH: 234px"><COL style="WIDTH: 70px"><COL style="WIDTH: 95px"><COL style="WIDTH: 46px"></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><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">First Name:</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Last Name:</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">GBU</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Team Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Workouts</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Total Minutes</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Bonus</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Jim</TD><TD>Jones</TD><TD>Personal Health Care</TD><TD>4Fun</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">180</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Jason</TD><TD>Smith</TD><TD>Personal Health Care</TD><TD>4Fun</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">450</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Katie</TD><TD>Doan</TD><TD>Oral Care</TD><TD>4Fun</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">180</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Jeff</TD><TD>Griffith</TD><TD>Personal Health Care</TD><TD>4Fun</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">275</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ffff00">Larry</TD><TD style="BACKGROUND-COLOR: #ffff00">Commins</TD><TD style="BACKGROUND-COLOR: #ffff00">Personal Health Care</TD><TD style="BACKGROUND-COLOR: #ffff00">4hr</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">150</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BACKGROUND-COLOR: #ffff00">Kim</TD><TD style="BACKGROUND-COLOR: #ffff00">Jones</TD><TD style="BACKGROUND-COLOR: #ffff00">Pet Care</TD><TD style="BACKGROUND-COLOR: #ffff00">4hr</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">300</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #ffff00">Jeff</TD><TD style="BACKGROUND-COLOR: #ffff00">Green</TD><TD style="BACKGROUND-COLOR: #ffff00">Corporate Function/GCO/All Other</TD><TD style="BACKGROUND-COLOR: #ffff00">4hr</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">210</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Frank</TD><TD>Grendel</TD><TD>Personal Health Care</TD><TD>A Burger a Day Keeps the Doctor Away</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">165</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Hank</TD><TD>Hill</TD><TD>Personal Health Care</TD><TD>A Burger a Day Keeps the Doctor Away</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">335</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Betsy</TD><TD>Ross</TD><TD>Personal Health Care</TD><TD>A Burger a Day Keeps the Doctor Away</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">180</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Sunny</TD><TD>Lipton</TD><TD>Personal Health Care</TD><TD>A Burger a Day Keeps the Doctor Away</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">300</TD><TD style="TEXT-ALIGN: right">Yes</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[rs2k]

Try using SUMIF;

Create a unique list of names from Col D and then in there adjacent cell use this formula;

=SUMIF(D2:D300,I2,E2:E300)

I used I2 for the unique name and the formula in J2,

HTH
Colin

 

[jhedges]

Colin,

Thanks for your help - I changed gears alittle and used a Pivot Table(something new to me) which worked great...

Jason