Help with a formula

[Hervinder]

Firsly apologies for the layout of the table below

The table is a relatively simple table which has the names and the points of
each month going across. Also the month the person started in column B, the
top row is the working days in each month.

What i want to do in the second table is sum the total points for each
person from the month they started up to the month in the cell. For example
Mr a started in feb so i cell c10 he would have zero because he didnt start
until feb. in cell d10 it would be just 20 and e10 it would be the sum of feb
and mar and f10 it would be the sum of feb, mar and apr and so on.

A B C D E
F G
1 Days 19 21 20 19 20
2 Name Start Mth Jan Feb Mar Apr May
3 Mr a Feb 10 20 30 40 50
4 Mr b Apr 15 25 35 45 55
5 Mr c Mar 5 15 25 35 45

Jan Feb Mar
Apr May
10 Mr a
11 Mr B

to complicate it even more i want the the result in each cell to be divided
by the total days up to the month in the cell. So in c10 it would still be
zero in d10 it would be divided by 21 and e10 it would be divided by the
total of 21 and 20 and f10 it would be divided by the total of 21,20 and 19.

sounds easy but i cannot think of an easy solution. I would appreciate if
anyone can help on the above.

many thanks
Hervinder

 

[Bob Phillips]

C10

=IF(DATEVALUE(INDEX($B$2:$B$5,MATCH($A10,$A$2:$A$5))&YEAR(TODAY()))>DATEVALUE(C$9&YEAR(TODAY())),0,
SUM(INDEX($C$2:$C$5,MATCH($A10,$A$2:$A$5)):INDEX(C2:C5,MATCH($A10,$A$2:$A$5)))/SUM($C$1:C$1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)