SUMIFS(INDEX(MATCH #Value

[Dtex20]

Hi Guys,

=SUMIFS(INDEX(Service!$E$4:$AM$45,MATCH(pay_table[@[Employee Number]],Service!$A$4:$A$45,0),0),Service!$E$3:$AM$3,">="&Data!$C$2,Service!$E$3:$AM$3,"<="&Data!$D$2)

This is my formula that works great, but i'm trying to add another parameter to the SUMIF. Which only adds values that match cell value 40. When i add this parameter i get #value , can someone explain to me why?

=SUMIFS(INDEX(Service!$E$4:$AM$45,MATCH(pay_table[@[Employee Number]],Service!$A$4:$A$45,0),0),Service!$E$3:$AM$3,">="&Data!$C$2,Service!$E$3:$AM$3,"<="&Data!$D$2,Service!$E$4:$AM$45,40)

Thanks,

 

[Domenic]

That's because the ranges being referenced are not all the same size...

[I][B]INDEX(Service!$E$4:$AM$45,MATCH(pay_table[@[Employee Number]],Service!$A$4:$A$45,0),0) [/B][/I]refers to a 1-Row by 35-column range

[I][B]Service!$E$3:$AM$3 [/B][/I] also refers to a 1-Row by 35-column range

[I][B]Service!$E$4:$AM$45 [/B][/I]refers to a 42-Row by 35-Column range

So, as you can see, the last range being referenced differs in size than the other ones.