Cumulative Summation of Data

[shriil]

Hi

I have a datasheet where I input the Overtime Hours clocked by each
employee. The table resembles the following


Date Name OT Hours
01-Oct-08 Simon 12
01-Oct-08 Ahmed 8
02-Oct-08 Dick 10
04-Oct-08 Ahmed 6
07-Oct-08 Simon 7
07-Oct-08 Dick 13
09-Oct-08 Simon 5

What I need is to get the cumulative hours clocked by each employee,
such as

Simon X Hours
Ahmed Y Hours
Dick Z Hours

Right now, I have to select each name and Hours manually and then add
the same.

Is there any formula for easing out this problem.

Thks for the help

 

[muddan madhu]

suppose the data is col A, Col B & Col C

in E1 you have names put this formula in F1 =SUMPRODUCT(--($B$2:$B
$8=E1)*($C$2:$C$8))

use ctrl + shift + enter

 

[Rick Rothstein]

You do not need to commit that formula with Ctrl+Shift+Enter... SUMPRODUCT
functions can be entered normally. And, since you are multiplying logical
expressions, you do not need the double unary...

=SUMPRODUCT(($B$2:$B$8=E1)*($C$2:$C$8))

committed normally with the Enter key will work as expected.

--
Rick (MVP - Excel)


suppose the data is col A, Col B & Col C

in E1 you have names put this formula in F1 =SUMPRODUCT(--($B$2:$B
$8=E1)*($C$2:$C$8))

use ctrl + shift + enter

 

[Don Guillett]

Look in the help index for SUMIF

 

[Bernd P]

Hello,

One further approach might be Afreq from:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[MartinW]

Hi Shriil,

One other way, with your example data in A1 to C7, try this.
D1 Simon
D2 Ahmed
D3 Dick
Put this formula in E1 =SUMIF($B$1:$B$7,D1,$C$1:$C$7)
and drag it down to E3

HTH
Martin

 

[shriil]

You do not need to commit that formula with Ctrl+Shift+Enter... SUMPRODUCT
functions can be entered normally. And, since you are multiplying logical
expressions, you do not need the double unary...

=SUMPRODUCT(($B$2:$B$8=E1)*($C$2:$C$8))

committed normally with the Enter key will work as expected.

--
Rick (MVP - Excel)


suppose the data is col A, Col B & Col C

in E1 you have names put this formula in F1 =SUMPRODUCT(--($B$2:$B
$8=E1)*($C$2:$C$8))

use ctrl + shift + enter










- Show quoted text -

The formula works ok. But then I have to write the employee names in a
seperate column (E1..) and put the formula in F1. Now the problem is
that the employee count is about 200 and not all employees do OT in a
given month. Thus manual inputting of each employee name in a seperate
column would become a tedious affair. Is there any way that excel
will pick up the names (instead of me entering the names manually) and
give the respective sum of OT hours, done by that particular employee
in two seperate columns?

 

[Don Guillett]

I don't quite understand. If desired, send your wb to my address below along
with snippets of these emails inserted in a newly inserted sheet and a full
explanation of what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

You do not need to commit that formula with Ctrl+Shift+Enter... SUMPRODUCT
functions can be entered normally. And, since you are multiplying logical
expressions, you do not need the double unary...

=SUMPRODUCT(($B$2:$B$8=E1)*($C$2:$C$8))

committed normally with the Enter key will work as expected.

--
Rick (MVP - Excel)


suppose the data is col A, Col B & Col C

in E1 you have names put this formula in F1 =SUMPRODUCT(--($B$2:$B
$8=E1)*($C$2:$C$8))

use ctrl + shift + enter










- Show quoted text -

The formula works ok. But then I have to write the employee names in a
seperate column (E1..) and put the formula in F1. Now the problem is
that the employee count is about 200 and not all employees do OT in a
given month. Thus manual inputting of each employee name in a seperate
column would become a tedious affair. Is there any way that excel
will pick up the names (instead of me entering the names manually) and
give the respective sum of OT hours, done by that particular employee
in two seperate columns?

 

[Don Guillett]

Sub sumuniquenamesFormula()
'make unique list
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "b").End(xlUp).Row
Range("d2:e" & lr).ClearContents
Range("b2:b" & lr).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("d2") _
, Unique:=True
'Get totals per name
flr = Cells(Rows.Count, "d").End(xlUp).Row
With Range("e2:e" & flr)
..Formula = _
"=sumif($b$2:$b$" & lr & ",d2,$c$2:$c$" & lr & ")"
..Value = .Value
End With
Application.ScreenUpdating = True
End Sub

 

[shriil]

Sub sumuniquenamesFormula()
'make unique list
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "b").End(xlUp).Row
Range("d2:e" & lr).ClearContents
Range("b2:b" & lr).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("d2") _
, Unique:=True
'Get totals per name
flr = Cells(Rows.Count, "d").End(xlUp).Row
With Range("e2:e" & flr)
.Formula = _
"=sumif($b$2:$b$" & lr & ",d2,$c$2:$c$" & lr & ")"
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- Show quoted text -

Thks again

shriil