Cumulative Summation of Data

  • Thread starter Thread starter shriil
  • Start date Start date
S

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
 
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
 
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
 
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
 
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 -
Click to expand...


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?
 
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 -
Click to expand...


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?
 
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
 
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 -
Click to expand...

Thks again

shriil
 
Back
Top