Sum data when considering several variables

Hi there

Have a spreadsheet whereby I need to calculate Pension on a salary, based on the source data meeting certain criteria.

Spreadsheet attached. Worksheet name 'Staff Costs', source data 'Staff Data'.

http://www.excelforum.com/attachment...1&d=1459353891

Cell V16
I've attempted to right a formula but it's not pulling in the desired value.

What I would like:
From 'Staff Data', I would like the formula to check:

Employee cell T13 (Staff Costs), from column G (Staff Data)
ABC cell T5 (Staff Costs), from column B (Staff Data)
Check the employee has started via Staff Data column M (as we're looking at Jan-16) is equal to 1
Start Date cell V5 (Staff Costs), from column I (Staff Data) - then add on 3 months which is when Pension cost becomes effective, assuming it's post Jan-16

THEN,

I would like the formula to return the sum of 'Salary x Pension %'

Hope this makes sense

Help if you can. I've spent a while on it with no luck

Thanks, Maddy

Try

=SUMPRODUCT(('Staff Data'!K3:$K$46)*('Staff Data'!B3:$B$46=$T$5)*('Staff Data'!G3:$G$46=$T$13)*('Staff Data'!M3:$M$46=1)*(DATE(YEAR('Staff Data'!$I$3:$I$46),MONTH('Staff Data'!$I$3:$I$46)+3,DAY('Staff Data'!$I$3:$I$46))<V$5)*('Staff Data'!M$3:M$46),('Staff Data'!$L$3:$L$46)/12)

Above gives result of 440 which agrees with calculation using filtering (assuming I got that right!)

In your original formula you had Salaries in twice.

=SUMIFS('Staff Data'!K2:$K$46,'Staff Data'!B2:$B$46,$T$5,'Staff Data'!G2:$G$46,$T$13,'Staff Data'!M2:$M$46,1)*(DATE(YEAR('Staff Data'!$I$3:$I$46),MONTH('Staff Data'!$I$3:$I$46)+3,DAY('Staff Data'!$I$3:$I$46))<V$5)*('Staff Data'!$K$3:$K$46*'Staff Data'!M$3:M$46*'Staff Data'!$L$3:$L$46/12)

Corrected .....

=SUMIFS('Staff Data'!K2:$K$46,'Staff Data'!B2:$B$46,$T$5,'Staff Data'!G2:$G$46,$T$13,'Staff Data'!M2:$M$46,1)*(DATE(YEAR('Staff Data'!$I$3:$I$46),MONTH('Staff Data'!$I$3:$I$46)+3,DAY('Staff Data'!$I$3:$I$46))<V$5)*('Staff Data'!M$3:M$46*'Staff Data'!$L$3:$L$46/12)

This now also returns 440