Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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 "nicolairob" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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 -- Gary "nicolairob" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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" wrote: 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 -- Gary "nicolairob" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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)) -- Gary "nicolairob" wrote in message ... 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" wrote: 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 -- Gary "nicolairob" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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) "nicolairob" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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) -- Gary "JMB" wrote in message ... 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) "nicolairob" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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 "Gary Keramidas" wrote: 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)) -- Gary "nicolairob" wrote in message ... 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" wrote: 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 -- Gary "nicolairob" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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" wrote: 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) -- Gary "JMB" wrote in message ... 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) "nicolairob" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM function Help Please
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 -- Gary "nicolairob" wrote in message ... 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 "Gary Keramidas" wrote: 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)) -- Gary "nicolairob" wrote in message ... 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" wrote: 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 -- Gary "nicolairob" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |