Sum function

[luckbox8]

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!!!

 

[dispelthemyth]

Excel Workbook
	A	B	C	D
1	Name	Site	hours
2	Mike Smith	Fort Collins	4	14
3	Mike Smith	Fort Collins	5
4	Mike Smith	Fort Collins	5
5	Mike Smith	SeaWorld	3
6	Mike Smith	SeaWorld	5
Sheet2

 

[farmerscott]

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]

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]

SUMPRODUCT would be another alternative

Excel 2010
	A	B	C	D
1	Name	Site	hours
2	Mike Smith	Fort Collins	4	14
3	Mike Smith	Fort Collins	5
4	Mike Smith	Fort Collins	5
5	Mike Smith	SeaWorld	3
6	Mike Smith	SeaWorld	5
Sheet3

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6=B2),--(C2:C6))

 

[dispelthemyth]

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

=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]

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

 

[dispelthemyth]

no need for the -- on the C2:C6 which i assume is an oversight

=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

=SUMPRODUCT(($A$2:$A$6=A2)*($B$2:$B$6=B2),C2:C6)

SUMPRODUCT would be another alternative

Worksheet Formulas Cell Formula D2 =SUMPRODUCT(--($A$2:$A$6=A2),--($B$2:$B$6=B2),--(C2:C6)) <tbody> </tbody>

<tbody>
</tbody>