I am putting together a timesheet - see example

Col D Col E
11 Time In 8:30 AM
12 Time Out 12:30 PM
13 Hours 4.0
14 Rate 1.5
15 Meal Break
16 Time In 1:00 PM
17 Time Out 3:00 PM
18 Hours 2.0
19 Rate 2.0
20 Total @ 1.5 0.5
21 Total @ 2.0 6.0

In E20, I wanted to calculate the number of hours worked where rate = 1.5
(similar for E21 but for rate=2.0)

I tried SUMIF, but it gave odd results (results expected should have been 4
hours @ 1.5 and 2 hours @ 2.0) using =SUMIF(E11:E19,1.5,E13) and
=SUMIF(E11:E19,2.0,E13) respectively.

A) - I cannot have more than 2 cells in the sumrange as they are not adjacent
B) - SUMIF results are dependent upon results of other formulae in the cells
C) - results not expected

Help anyone? Sorry if this is clear as mud.


Its a bit complicated but

suggest you re-arrange into columns
a
time in
b
time out
etc

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bibi" wrote in message
...
I am putting together a timesheet - see example

Col D Col E
11 Time In 8:30 AM
12 Time Out 12:30 PM
13 Hours 4.0
14 Rate 1.5
15 Meal Break
16 Time In 1:00 PM
17 Time Out 3:00 PM
18 Hours 2.0
19 Rate 2.0
20 Total @ 1.5 0.5
21 Total @ 2.0 6.0

In E20, I wanted to calculate the number of hours worked where rate = 1.5
(similar for E21 but for rate=2.0)

I tried SUMIF, but it gave odd results (results expected should have been
4
hours @ 1.5 and 2 hours @ 2.0) using =SUMIF(E11:E19,1.5,E13) and
=SUMIF(E11:E19,2.0,E13) respectively.

A) - I cannot have more than 2 cells in the sumrange as they are not
adjacent
B) - SUMIF results are dependent upon results of other formulae in the
cells
C) - results not expected

Help anyone? Sorry if this is clear as mud.


Its a bit complicated but

You are trying to squeeze all your data into 2 columns, but if you
have a lot of employees or dates it will be better to arrange your
sheet to have names/dates going down on a different row, and to have
many of the data items in your example in different columns. So,
"Time_In" is in one column, "Time_Out" in another, "Hours" in yet
another, as is "Rate" etc. This way you would find it easier to apply
functions like SUMIF.

Hope this helps.

Pete

Then you would fine

On Jan 30, 2:15*pm, Bibi wrote:
I am putting together a timesheet - see example

* * * * * * Col D * * * * * * * *Col E
11 * * *Time In 8:30 AM
12 * * *Time Out * * * *12:30 PM
13 * * * Hours *4.0
14 * * * *Rate *1.5
15 * * *Meal Break * * *
16 * * *Time In 1:00 PM
17 * * *Time Out * * * *3:00 PM
18 * * * Hours *2.0
19 * * * *Rate *2.0
20 * * Total @ 1.5 * * * * * * *0.5
21 * * *Total @ 2.0 * * * * * * 6.0

In E20, I wanted to calculate the number of hours worked where rate = 1.5
(similar for E21 but for rate=2.0)

I tried SUMIF, but it gave odd results (results expected should have been 4
hours @ 1.5 and 2 hours @ 2.0) *using =SUMIF(E11:E19,1.5,E13) and
=SUMIF(E11:E19,2.0,E13) respectively.

A) - I cannot have more than 2 cells in the sumrange as they are not adjacent
B) - SUMIF results are dependent upon results of other formulae in the cells
C) - results not expected

Help anyone? *Sorry if this is clear as mud.

Its a bit complicated but

hmm thought as much :S

thanks for your quick response.

"Don Guillett" wrote:

suggest you re-arrange into columns
a
time in
b
time out
etc

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bibi" wrote in message
...
I am putting together a timesheet - see example

Col D Col E
11 Time In 8:30 AM
12 Time Out 12:30 PM
13 Hours 4.0
14 Rate 1.5
15 Meal Break
16 Time In 1:00 PM
17 Time Out 3:00 PM
18 Hours 2.0
19 Rate 2.0
20 Total @ 1.5 0.5
21 Total @ 2.0 6.0

In E20, I wanted to calculate the number of hours worked where rate = 1.5
(similar for E21 but for rate=2.0)

I tried SUMIF, but it gave odd results (results expected should have been
4
hours @ 1.5 and 2 hours @ 2.0) using =SUMIF(E11:E19,1.5,E13) and
=SUMIF(E11:E19,2.0,E13) respectively.

A) - I cannot have more than 2 cells in the sumrange as they are not
adjacent
B) - SUMIF results are dependent upon results of other formulae in the
cells
C) - results not expected

Help anyone? Sorry if this is clear as mud.


Its a bit complicated but