How do I show the only the latest date for a particular name ?

Use this to get the Latest Start Cert date

=MAX(INDEX(Table1[Cert Start date]*(Table1[Name]=A4),,))

Select allOpen in new window

Rolling date on Calculation is 4 years before Latest Start Cert.
That is different from the Rolling 4 years on Data entry, where it is for each cert, not the latest.
The sumifs formula must look at Cert Start date for both.

=SUMIFS(Table1[Days taken],Table1[Name],$A4,Table1[Cert Start date],"<="&$B4,Table1[Cert Start date],">="&$C4)

Select allOpen in new window

EE-TRR-Calculations.xlsx

Ah, brilliant. Tried all that and worked wonderfully. Thank you. Now one variation on this. Turns out that the total days taken should be inclusive of all the days - including the days taken on the most recent cert..so therefore how do I adapt the formula so that it calculates the days between the rolling 4 year date for the latest cert and the Cert finish date? I need to add a column that calculates the rolling 12 month total. I've done that by adapting your formula for the cumulative days Cumulative Days - 12 months column.
The formula needs to show the total number of days leave taken based on the difference between the    most recent Cert Finish Date and  their most current rolling 4 year (Calculations - D4:D9) and 12 month date (Calculations G4:G9). I tried that with the revised formula and it came out the same as yours even though you used a different date - what am I missing here? Thanks
EE-TRR-Calculations-vers-02.xlsx

The dates for each person are close, so 4 years will include all, no matter which dates are used.
Rolling is based on Latest Start Date, and all finish dates are less than 12 month before, so it will include all also.
Cert Finish Date is the maximum date, so all dates, both Cert Start Date and Cert Finish Date will be less than that, and the criteria can be removed.
So it can be this for 12 month (G4)
=SUMIFS(Table1[Days taken],Table1[Name],$A4,Table1[Cert Finish Date],">="&$E4)
and this for 4 years (H4)
=SUMIFS(Table1[Days taken],Table1[Name],$A4,Table1[Cert Finish Date],">="&$D4)

The dates can and probably will vary a lot so I need to have the stability built in to ensure it can cope with lots of certs over the 4 year period..let me try this. :-) thank you :-)

Above formulas check if the finish dates are greater than the latest rolling period start.
It all depend on how you define the rolling periods.
Based on Start dates it will include more than when using Finish dates.

Basically the days leave calculated is based on the following: Every time someone gets a cert, their 4 year rolling date is set to 4 years prior to that date and their 12 months rolling date is set 12 months to that date. Therefore the days allowed at that point are based on the total number of days taken in the last 4 years from the latest 4 year rollback and the final date of their most recent cert...ditto for 12 months...

Gets a cert = Finish date.
Then the rolling periods must be calculated from the Latest Finish Date.
EE-TRR-Calculations-vers-02.xlsx

Oh, I wish...that's how the policy rolls. I did check..The rollback is from the start date but the days are calculated to include ALL days...

Then this how it must be.
EE-TRR-Calculations-vers-02.xlsx

GRRR. This is driving me nuts! I've gone back to the drawing board again...I've put my calculations in the sheet marked Overlapping calculations...
Brian should have 165 days as his total in L3. Starting date (4 years rollback) is in cell J3 i.e. 23/3/2016. His finish date is 4/5/2016.
However he has a cert that covers from 1/12/2011 to 30/6/2012. That means that the days between 23/3/2012 and 30/6/2012 (99 days) should be added to that 99 so he should have a total of 165 instead of the 66 I have
How can I incorporate that calculation? My formula is in cell L3.
EE-TRR-Calculations-vers-03.xlsx

Then column F (Days taken) can not be used, and the days have to be calculated instead.
Days taken are inclusive start and finish day, so 1 has to be added to the day difference.
So from 23/3/2012 to 30/6/2012 are 100 days.
See array formula (Enter with Ctrl+Shift+Enter) in M6.
=SUM(IF(Table2[Cert Finish Date]>=$J$3,Table2[Cert Finish Date]+1-IF(Table2[Cert Start date]>=$J$3,Table2[Cert Start date],$J$3),0))
EE-TRR-Calculations-vers-03.xlsx

Brilliant! And at the risk of driving you completely nuts...:-) how do I build in the functionality of it adjusting for the different names as per the Calculations sheet as I copy it down?

See formula in H4 on Calculations.
=SUM(IF(Table1[Name]=$A4,1,0)*IF(Table1[Cert Finish Date]>=$D4,Table1[Cert Finish Date]+1-IF(Table1[Cert Start date]>=$D4,Table1[Cert Start date],$D4),0))
EE-TRR-Calculations-vers-03.xlsx

ABSOLUTELY MAGNIFICENT :-) And I assume I can just copy the formula to the 12 month column and just tweak for the cells it should be referencing?

This has saved me so much time and effort - it's priceless. Can't recommend the help of this expert enough.. THANK YOU.