Formula based on contents of adjacent cells?

Hi All,

I have a complex question but I'll have to give you a bit of background relating to the problem:

I'm using an excel spreadsheet for my work to record the hours that agency drivers work. The spreadsheet records (in column D of the attached file) the day of the week they work. The day of the week determines the hourly rate the driver gets paid, this bit I have done with a formula that works ok.

After this column the next column (E) records the amount of hours they work.

Should the drivers hours overlap to the another day, for example say 11pm on a Monday and finishes on 8am on Tuesday then the day is recorded in column F and the amount of hours also recorded in G.

Any overtime is recorded in column H.

The rates (where this problem lies) are in columns I & J.

The rates differ depending on the day that they work, for example weekends, bank holidays etc. What I want to do at the bottom of the spreadsheet is add up all the hours but I want totals of all the hours at the different rates.

My attached spreadsheet would probably explain it better, if you need to know more feel free to let me know.

Thanks
Lewis

1) First, your problem in column L is that the formulas in J & K are using a text value of "£ 0.00". You should really change it to just 0 so it's a numeric and let the cell formatting add the pound sign and spaces.

2) What I think you're looking for is the SUMIF function. So E24 would be: =SUMIF(I$3:I$18,G24,E$3:E$18)+SUMIF(J$3:J$18,G24,G$3:G$18)+SUMIF(K$3:K$18,G24,H$3:H$18) You can then drag the formula down to the other rows for it to work w/ the other rates.