# Userdefined Function not working in SUMIFS Function • ### Question

• I created below mentioned userdefined function to replace "INDIRECT" function which used in SUMIFS function but UDF is working correctly in SUM function but it is not working in SUMIFS function.

Refer the below example.

=SUM(INDVBA("A1:A"&G1))  -----> It is working

=SUMIFS(INDVBA("A1:A"&G1),B4:B13,3,C4:C13,10) ------> It is not working and it return "#VALUE" error in cell.

Function INDVBA(strcell As String)
INDVBA = Range(strcell)
End Function

Bala

Thursday, March 31, 2016 2:57 PM

• SUMIFS expects a range as first argument, but your version of INDVBA returns an array of values, not a range.

Your SUM formula works because SUM accepts both a range and an array as argument.

Change the function to

```Function INDVBA(strcell As String)
Set INDVBA = Range(strcell)
End Function```

to make it return a range. Your SUMIFS formula should then work (you'll have to recalculate it).

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Thursday, March 31, 2016 4:23 PM
Thursday, March 31, 2016 3:40 PM

### All replies

• SUMIFS expects a range as first argument, but your version of INDVBA returns an array of values, not a range.

Your SUM formula works because SUM accepts both a range and an array as argument.

Change the function to

```Function INDVBA(strcell As String)
Set INDVBA = Range(strcell)
End Function```

to make it return a range. Your SUMIFS formula should then work (you'll have to recalculate it).

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Thursday, March 31, 2016 4:23 PM
Thursday, March 31, 2016 3:40 PM
• Hi Hans Vogelaar,

Thank You Very Much !!!!

Now UDF is working correctly in SUMIFS function.

Bala

Thursday, March 31, 2016 4:26 PM