This has beaten me. Can you help?

[tjack_87]

I have tried for hours but cant figure it out. im hoping you can help.

I have two spreadsheets. Spreadsheet 1 (SS1) that tells me a list of employee absence. column A has employee number and B has absence start date. The same employee may appear more than once if they have had more than one absence.

Spreadhseet two (SS2) shows me the rate of pay each colleague should get for a particular week of absence. If there is a rate for a second week the colleague will appear again on a second row. Column A Employee Number, B is Week Start date, C is Week end date and D is rate of pay.

I want Excel to look in Column A on SS2 for an Employee number (contained in SS1), and if the absence Start in SS1 falls within the week start and end in SS2 for that colleague, display the rate of pay that they should receive.

i hope this makes sense and im sorry if the formula is simple. the colleagues arent in the same order on the two spreadsheets and a colleague may appear more times in one SS than they do in the other, which is causing me the confusion

 

[Chameleon64]

In cell C2 on SS1 type:

=SUMIFS('SS2'!$D:$D,'SS2'!$A:$A,A2,'SS2'!$B:$B,"<="&B2,'SS2'!$C:$C,">="&B2)

I've assumed that the rate of pay weeks don't overlap for a particular employee.