Sumproduct not working

[rahildhody]

Hi,

I'm trying to calculate the headcount & salary by allocation for a 5yr period where the salaries change every year, but the headcount allocation stays the same. I think im doing something very silly as the sumproduct formula that im using doesnt seem to work.

Frmula used in F20 calculates correctly based on End of Month dates & calculates the count correctly as the period increases.

The same cannot be said when im trying to calculate the salary cost for the same period:

I'm sure its something really silly between the arrays but im not able to figure out what it is. Could someone please assist.
This is the fomula im trying to use in F28:

=SUMPRODUCT($N$5:$T$14*($D$5:$D$14=$D28)*($N$4:$T$4=$E28)*($F$5:$F$14<=F$18)*$G$5:$L$14*($G$4:$L$4=F$17))


Thanks in advance.

Cheers,
Rahil

 

[rahildhody]

not sure if it updated, but using Microsoft 365 on Windows

 

[RoryA]

Try (untested):

=SUMPRODUCT($N$5:$T$14*($D$5:$D$14=$D28)*($N$4:$T$4=$E28)*($F$5:$F$14<=F$18)*INDEX($G$5:$L$14,0,MATCH(F$17,$G$4:$L$4,0)))

 

[rahildhody]

Try (untested):

=SUMPRODUCT($N$5:$T$14*($D$5:$D$14=$D28)*($N$4:$T$4=$E28)*($F$5:$F$14<=F$18)*INDEX($G$5:$L$14,0,MATCH(F$17,$G$4:$L$4,0)))

OMG! thank you so much! that worked like a charm! knew it was something really silly. thank you for providing me with a solution.

 

[RoryA]

Glad we could help.