none
Userdefined Function not working in SUMIFS Function RRS feed

  • 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

Answers

  • 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 Balaramji 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 Balaramji 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