SUM function Help Please

[nicolairob]

Hi,
I am having problems with summing some data with multiple criteria. It
appears that SUMIF will not work due to all ranges required to be the same
size and shape.
Example:

"HOURS WORKED"

(A) (B) (C) (D)
(E)

1. 11/1/07 11/2/07 11/3/07
11/4/07

2. Mary 8.0 4.5 6.0
3.5

3. Tom 7.0 2.5 3.0
6.0

4. Cindy 3.5 8.0 9.0
7.0

5. Rob 4.0 5.5 6.5
8.0


Calculation: I need to know the total hours Tom worked from 11/2/07 -
11/4/07.
Please Help and thanks so much,
Rob

 

[nicolairob]

Sorry, I guess it did not display as I originally typed it. First time on
board here - Is it possible to attach or paste an actual spreadsheet sample
in the message text?
Rob

 

[Gary Keramidas]

may be a simpler way, but would something like this work for you?

=SUMIF(B1:Z1,">="&B1&"",B3:Z4)-SUMIF(B1:Z1,">"&E1&"",B3:Z4)


i assumed dates were in B1:Z1 and you wanted the totals from B1 to E1

 

[nicolairob]

Hi Gary,
Yes, the dates were in B1:Z1. However, on a separate worksheet "Sheet 2" I
will have the date 11/2/07 in (A1) and 11/4/07 in (B1). Employee names in
(C1) In (D1), I want the calculation from "Sheet 1" as presented below:
I want to know how many hrs Tom(A3) worked from 11/2/07 - 11/4/07 (C3:E3)
Now, I have to link to the name "Tom" and not the cell, as new entries on
that sheet will shift his name accordingly. Also, when I change the dates in
"Sheet 2 (A1) and (B1), it will need to provide that time range summation in
(D1).
Thanks,
Rob

 

[Gary Keramidas]

ok, on sheet1, name the range B3:Z3 Tom, and do the same for every other name

then try this formula in column d on sheet2

=SUMIF(Sheet1!B1:Z1,">="&A1&"",INDIRECT(C1))-SUMIF(Sheet1!B1:Z1,">"&B1&"",INDIRECT(C1))

 

[JMB]

Assuming your data is in A1:E5 (names in A2:A5, dates in B1:E1, and hours are
in B2:E5) and A11=Tom, B11=11/2/7, C11=11/4/7, you could try:

=SUMPRODUCT((A2:A5=A11)*(B1:E1>=B11)*(B1:E1<=C11)*B2:E5)

or given the same assumptions about the location of the data, I'm not
completely following why the following Sumif suggestion does not work for you
(as Gary originally recommended). If addition rows are added/deleted from
your table, XL will update this formula accordingly. The only problem will
be if you type another name over "Tom" or copy/paste "Tom" to another
location in the table (instead of cut/paste - which would work fine). Also,
I believe the sumif statement will calculate faster.

=SUMIF(B1:E1,">="&B11,B3:E3)-SUMIF(B1:E1,">"&C11,B3:E3)

 

[Gary Keramidas]

i did have a typo in the original post, but didn't correct it because the op
changed their request

=SUMIF(B1:Z1,">="&B1&"",B3:Z4)-SUMIF(B1:Z1,">"&E1&"",B3:Z4)
s/b
=SUMIF(B1:Z1,">="&B1&"",B3:Z3)-SUMIF(B1:Z1,">"&E1&"",B3:Z3)

 

[nicolairob]

Gary,
That works great as long as I do not sort rows on "Sheet 1." If I insert
rows, the name range will move accordingly, but if I sort names in alpha
order for example, it remains constant regardles of whose name is in column
A. I need it to give me "Toms" hours even if I sort rows.
Thank you so much for helping me with this!
Rob

 

[JMB]

I assumed as much. I don't think I'm completely following the OP's concern
about "Tom" being shifted due to additional entries.

 

[Gary Keramidas]

probably easier for me to code it for you. what i did was write code to create a
formula. there's a shorter way, but creating the formula in the cell will show
you how the result was accomplished.

i use sheet1 and sheet2 in my code and you would need to change the names if
yours are named differently.

not sure what you know about vba, but open the vb editor and under project
explorer you should see all of the sheets listed, double click on sheet2 and
paste the following code.
then go to sheet2 and change the values in A1: c1 and see if you get the correct
result.

i also pasted the code with line numbers in case any of it wrapped incorrectly
so you can try to place the line breaks..




Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim rngfound As Range
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

If Not Intersect(Target, ws2.Range("C1")) Is Nothing Then
With ws.Range("A1:A" & lastrow)
Set rngfound = .Find(ws2.Range("C1"), lookat:=xlWhole)
If Not rngfound Is Nothing Then
ws2.Range("D1").Formula = "=SUMIF(Sheet1!B1:Z1," & """>=""" & _
"&A1" & ",Sheet1!B" & rngfound.Row & ":Z" & rngfound.Row & _
")-SUMIF(Sheet1!B1:Z1," & """>""" & "&B1" & ",Sheet1!B" & _
rngfound.Row & ":Z" & rngfound.Row & ")"
End If
End With
End If
End Sub

=============================================================

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim rngfound As Range
10 Set ws = Worksheets("Sheet1")
20 Set ws2 = Worksheets("Sheet2")
30 lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

40 If Not Intersect(Target, ws2.Range("C1")) Is Nothing Then
50 With ws.Range("A1:A" & lastrow)
60 Set rngfound = .Find(ws2.Range("C1"), lookat:=xlWhole)
70 If Not rngfound Is Nothing Then
80 ws2.Range("D1").Formula = "=SUMIF(Sheet1!B1:Z1," & """>=""" &
_
"&A1" & ",Sheet1!B" & rngfound.Row & ":Z" & rngfound.Row
& _
")-SUMIF(Sheet1!B1:Z1," & """>""" & "&B1" & ",Sheet1!B" &
_
rngfound.Row & ":Z" & rngfound.Row & ")"
90 End If
100 End With
110 End If
End Sub