Sum function

luckbox8

New Member
Joined
Jan 7, 2014
Messages
5
Hi Everyone,

I have a very long spreadsheet and need to sum the total hours worked by each employee based on which worksite they worked.

I have the data sorted in columns by employee, then by worksite, then by hours. My question is how can I get the "sum" of hours by worksite? Here is an example below:

Mike Smith Fort collins 4
Mike Smith Fort collins 5
Mike Smith Fort collins 5
Mike Smith SeaWorld 3
Mike Smith SeaWorld 5

How can I get it to give me 14 hours for Fort collins and 8 hours for SeaWorld????

This is a basic example but the real spreadsheet is 1000's of rows so I hate manually summing each week so THANKS!!!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
Excel Workbook
ABCD
1NameSitehours
2Mike SmithFort Collins414
3Mike SmithFort Collins5
4Mike SmithFort Collins5
5Mike SmithSeaWorld3
6Mike SmithSeaWorld5
Sheet2
 

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
Hello,

welcome to the forum.

Try using SUMIFS.

example =SUMIFS(C:C,A:A,A1,B:B,B1) and copy down. You could then-

1.do a copy and paste special into a separate area of your worksheet.
2. remove the duplicates via the "Data" tab

then you will have a summary.

Hope that helps

FarmerScott
 

luckbox8

New Member
Joined
Jan 7, 2014
Messages
5
Yeah but this formula you added had the specific row ranges for this example only. On the REAL spreadsheet it is 1000's of row of data so this formula wouldnt work unless i manually type in each range... I dont think this helps unless I am not comprehending your solution....
 

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484

ADVERTISEMENT

SUMPRODUCT would be another alternative

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Name</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Site</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">hours</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Mike Smith</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fort Collins</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Mike Smith</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fort Collins</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Mike Smith</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fort Collins</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Mike Smith</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">SeaWorld</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Mike Smith</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">SeaWorld</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$A$2:$A$6=A2</font>),--(<font color="Red">$B$2:$B$6=B2</font>),--(<font color="Red">C2:C6</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
the range is specific to your sample data

You can change it

e.g. if you know you never have more than 10,000 rows use

Code:
=SUMIFS($C$2:$C$10000,$A$2:$A$10000,$A2,$B$2:$B$10000,$B2)

Yeah but this formula you added had the specific row ranges for this example only. On the REAL spreadsheet it is 1000's of row of data so this formula wouldnt work unless i manually type in each range... I dont think this helps unless I am not comprehending your solution....
 

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
no need for the -- on the C2:C6 which i assume is an oversight

Code:
=SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6=B2),C2:C6)
suffices,

its only needed to turn True/False to 1/0

Alternatively you could use

Code:
=SUMPRODUCT(($A$2:$A$6=A2)*($B$2:$B$6=B2),C2:C6)
SUMPRODUCT would be another alternative


Worksheet Formulas
CellFormula
D2=SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6=B2),--(C2:C6))

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,436
Members
417,209
Latest member
Agbarker

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
Top