Sum function

luckbox8

New Member
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
Excel Workbook
ABCD
1NameSitehours
2Mike SmithFort Collins414
3Mike SmithFort Collins5
4Mike SmithFort Collins5
5Mike SmithSeaWorld3
6Mike SmithSeaWorld5
Sheet2

farmerscott

Well-known Member
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
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

SUMPRODUCT would be another alternative

dispelthemyth

Well-known Member
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....

luckbox8

New Member
Thanks!! I think this has got me on the right track

dispelthemyth

Well-known Member
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:

Replies
0
Views
187
Replies
3
Views
225
Replies
2
Views
270
Replies
29
Views
638
Replies
0
Views
229

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.

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

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