Using UDF with SUMPRODUCT
Results 1 to 8 of 8

Thread: Using UDF with SUMPRODUCT
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,734
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Using UDF with SUMPRODUCT

    I wrote this function, while working on another problem on this site and ran into one of my own. This function returns an array but when I try and use it with SUMPRODUCT, I get a VALUE error, any idea on how to do something like this. I have tried this returning an array and not returning an array and nothing seems to work:

    Code:
    Option Base 1
    Function IsNotFormula(c As Range) As Variant
    Dim y As Range, chkeach As Boolean, z1(), zcount As Long
    ReDim z1(c.Cells.Count)
    zcount = 1
    For Each y In c
        chkeach = y.HasFormula
        z1(zcount) = Not (chkeach)
        zcount = zcount + 1
    Next y
    IsNotFormula = z1
    End Function
    I was kind of hoping to use this like the other IS.... functions

    Using ISNUMBER, this works:
    =SUMPRODUCT(--ISNUMBER(A1:A3),B1:B3)

    but this doesn't:
    =SUMPRODUCT(--isnotformula(A1:A3),B1:B3)

    What do I need to do different to make it behave like the other IS functions?
    Office 2010/365

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You'd need to return it as an array, as sumproduct will not work with singular values pitched against an entire range (array) of data.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Using UDF with SUMPRODUCT

    I suppose you get #VALUE!. The reason for that the UDF produces an array with comma-separated truth values from a vertical range. The proper result should be an array of items separated with semi-colons.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using UDF with SUMPRODUCT

    It will work, but your UDF returns a 1d array (denoted by commas) and you're trying to multiply this by a 2d array (denoted by semicolons). It returns a value, but not the correct one as the return array is not the correct one.

    Try the following:

    Function IsNotFormula(ByRef c As Range) As Variant
    Dim y As Range, z1() As Boolean, zcount As Long
    ReDim z1(0 To c.Cells.Count - 1, 1 To 1)
    For Each y In c
    z1(zcount, 1) = Not y.HasFormula
    zcount = zcount + 1
    Next y
    IsNotFormula = z1
    End Function


    =SUMPRODUCT(IsNotFormula(C1:C3)*(D1:D3))

    This obviously has implications for what you are feeding your array in terms of a dynamic solution.

    Here's something odd. If I use:

    =SUMPRODUCT(TRANSPOSE(IsNotFormula(G1:I1))*(G2:I2))

    The cell displays the incorrect result. If I look at the return array, it's correct. If I hit the function wizard, the wizard shows the correct answer! The following also shows the correct answer:

    =SUM(TRANSPOSE(IsNotFormula(G1:I1))*(G2:I2))


  5. #5
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,734
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Using UDF with SUMPRODUCT

    Thank you very much!!
    Office 2010/365

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using UDF with SUMPRODUCT

    You are welcome.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Using UDF with SUMPRODUCT

    Quote Originally Posted by NateO
    It will work, but your UDF returns a 1d array (denoted by commas) and you're trying to multiply this by a 2d array (denoted by semicolons). It returns a value, but not the correct one as the return array is not the correct one.

    Try the following:...

    =SUMPRODUCT(IsNotFormula(C1:C3)*(D1:D3))

    This obviously has implications for what you are feeding your array in terms of a dynamic solution.

    Here's something odd. If I use:

    =SUMPRODUCT(TRANSPOSE(IsNotFormula(G1:I1))*(G2:I2))

    The cell displays the incorrect result. If I look at the return array, it's correct. If I hit the function wizard, the wizard shows the correct answer! The following also shows the correct answer:

    =SUM(TRANSPOSE(IsNotFormula(G1:I1))*(G2:I2))

    What is the big surprise, Nate? Both the Sum and SumProduct versions will require control+shift+enter because of the presence of TRANSPOSE, just like with an IF(). Not sure, but IsNotFormula is (I didn't test it) probably not returning a proper array. It should return an array with semi-colon as delimiter if it is applied to a vertical range and an array with comma if it's applied to a horizontal range, obliviating the call for TRANSPOSE.

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using UDF with SUMPRODUCT

    Hello Aladin,

    It's the correct array in terms of dimensions (at least if you use the udf I posted).

    I used transpose() because the udf I wrote returns a 2d array and it's easier to implement transpose() on 1d arrays then bugger around with dimension testing and dynamic array dimensioning... Can be done, I just didn't feel like it...

    Did not realize transpose() would require the array-enter with Sumproduct()...

    Edit: Yep, Transpose() is the problem! Ugh, so much for Sumproduct() in this instance.

    Thanks for showing me the way.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •