# SUMIFS(INDEX(MATCH #Value

#### Dtex20

##### Board Regular
Hi Guys,

Code:
``=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?

Code:
``=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,

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
That's because the ranges being referenced are not all the same size...

Code:
``````[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.

Last edited:

Replies
6
Views
222
Replies
1
Views
76
Replies
6
Views
190
Replies
8
Views
203
Replies
9
Views
119

1,203,052
Messages
6,053,233
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back