Index and Match Formula

[davidhall80]

Column A is 'Employee Name'. Column B is Hire Date by week. Beginning in column D and Row 2 and ending 52 columns to the right are weeks of the year paid. In column C, I want to add the three weeks of pay since hire date.

example...


..........A...........B..........C...........D...........E.............F..............G.........

1.....................................................................................................

2. .......Pay By Weeks...................1............2............3................4........

3.......................................................................................................

4.Employee...Hire ....Sum.of.3.............................................................
..................Week..............................................................................
5. John..........1.........................78.52........96.45.......45.35.........62.15

6. Ricky.........3.......................................................56.25..........78.15

7.Scott..........1.........................59.15.......14.06........78.51..........96.15

8.Tanya.........2........................................59.45.........96.15.........104.59





What I want in column C is the sum of three weeks of pay beginning with thier hire data. Because Johns hire week was 1, I would add his week 1 pay (78.52) plus week two pay (96.45) plus week three pay (45.35). Because Tanya's Hire Week was two, I would add 59.45 and 96.15. I've tried to do an Index Match formula but am unsuccessful if different employees have the same hire week. Any help at all would be appreciated. Thanks

 

[Domenic]

Assuming that for Tanya Week 2, 3, and 4 would be summed, try...

C5, copied down:

=SUM(INDEX(D5:BC5,MATCH(B5,$D$2:$BC$2,0)):INDEX(D5:BC5,MATCH(B5,$D$2:$BC$2,0)+2))

If the hire date is Week 51 or 52 and you'd like to sum the available pay, try the following instead...

=SUM(INDEX(D5:BC5,MATCH(B5,$D$2:$BC$2,0)):INDEX(D5:BC5,MIN(COLUMNS($D$2:$BC$2),MATCH(B5,$D$2:$BC$2,0)+2)))

Hope this helps!

 

[davidhall80]

Thanks it worked.....appreciate it

 

[davidhall80]

Thanks a million. It worked