Date Based SumIf Criteria

Workbook - Pensions Calc.xlsx

Hi

This is an inherited spread sheet and I've spotted a flaw that I'm not sure how to correct

Attached spread sheet has two tabs, Staff Data, Staff Costs

Staff Data lists Current Employees, date started etc and 'Pension' Contribution %

The Staff Costs tab has some formula in it which sums up Staff Data

Employees are only enrolled in the company Pension after completing 3 month probation period, therefore I need a formula that calculates from start date (col F) to insure it either falls prior to the month OR 3 month after in the relevant yr, i.e. 2014, e.g. All current employees who started 3 months before Jan-14 are entitled to Pension, any new employees will be entitled to the Pension 3 months from their start date

Any help would be grateful

Many thanks, Maddy

If I get hired on 5/15, does my entitlement to benefits begin in August or October?

(3 month anniversary would be 8/15, just clarifying calculation)

Maybe change you date range calculation to include the 3 months:

=IF(AND(L$1>=DATE(YEAR($F3),MONTH($F3)+3,DAY($F3)),L$1<$G3),$C3,IF(AND(MONTH(L$1)&YEAR(L$1)=MONTH($F3)+3&YEAR($F3),L$1<$G3),$C3*1-((DAY($F3)-1)/DAY(EOMONTH(L$1,0))),0))

it would probably roll to October, but yea from a formula point of view it would be 8/15

will try out your calc below

sorry for late reply

Thanks, Maddy

Thank you for your help with the formula above

However, I think I should have explained more clearly. It is the Formula Calc in 'Staff Costs' tab that requires amending to pull through the variant of +3 months

e.g. Cell D12 in 'Staff Costs' tab needs to be amended for the +3 months where applicable

I've tried using the one above but can't get it to work through

Your help is appreciated

Thanks, Maddy

Oh okay. I wrote the above formula because it looked like there was an error in the formulas for L3:W39.

The employees with a start date of 1/2/2014 are showing as having benefits as of 2/1/2014. The formula I wrote corrects the issue by only giving benefits at the 3 months +.

Correctly assigning benefits on the front page eliminates the need to change the formulas on the back page.

I think we are still at cross purposes

The 1 in those cells acts as a 'count' for FTE and Headcount numbers, therefore that formula does not need to change as to have a '1' in the start month is accurate. However, in cell D12 (for example) on the 'Staff Costs' tab, I need the formula to change so that when it calculates an amount it considers that it's 'start date' + 3 months

Does that make sense?

Many thanks, Maddy

I think I just about have it. The arrays in lines 21, 29, 37 etc should total Pension as such:

=SUMPRODUCT(('Staff Data'!$D$2:$D$40='Staff Costs'!$B$18)*(DATE(YEAR('Staff Data'!$F$2:$F$40),MONTH('Staff Data'!$F$2:$F$40)+3,DAY('Staff Data'!$F$2:$F$40))<D$6)*('Staff Data'!$H$2:$H$40*'Staff Data'!L$2:L$40*'Staff Data'!$I$2:$I$40/12))

With the bold reference changing depending on the job title.

This statement says:

Give me the sum of these criteria:
All lines that match job title
Of those lines that match, all lines whose hire dates plus three months are less than month header
Of those lines that match, Salaries * FTE * Pension% ÷ 12

The overall totals in 9:16 can stay as they are.

hello there

Thank you for persevering with this for me

I've worked it through the templates and it's working perfectly

and yes, it was cell D13 as the total feeding of the others you mentioned - be helpful if I gave the proper cell references..

Thanks again, Maddy