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

1. 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?

2. 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.

3. 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. 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. Re: Using UDF with SUMPRODUCT

Thank you very much!!

6. Re: Using UDF with SUMPRODUCT

You are welcome.

7. Re: Using UDF with SUMPRODUCT

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. Re: Using UDF with SUMPRODUCT

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.

User Tag List

Posting Permissions

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