Totally confused by SUMIF

[Bibi]

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

 

[Don Guillett]

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

 

[Pete_UK]

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

 

[Bibi]

hmm thought as much :S

thanks for your quick response.