Totalling Data Based on Criteria

J

jhedges

Board Regular
Joined
May 27, 2009
Messages
208
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Try using SUMIF;

Create a unique list of names from Col D and then in there adjacent cell use this formula;
Code: 
=SUMIF(D2:D300,I2,E2:E300)

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

HTH
Colin
 
Upvote 0
Colin,

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

Jason
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
350
esben
E
V
  • Question
Issue with scraping with VBA
Replies
1
Views
512
John_w
J
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A

Forum statistics

Threads
1,215,029
Messages
6,122,760
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top