Using UDF with SUMPRODUCT

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
20,301
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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.
 
Upvote 0
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:

<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> IsNotFormula(<SPAN style="color:darkblue">ByRef</SPAN> c <SPAN style="color:darkblue">As</SPAN> Range) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> y <SPAN style="color:darkblue">As</SPAN> Range, z1() <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Boolean</SPAN>, zcount <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">ReDim</SPAN> z1(0 <SPAN style="color:darkblue">To</SPAN> c.Cells.Count - 1, 1 <SPAN style="color:darkblue">To</SPAN> 1)
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> y <SPAN style="color:darkblue">In</SPAN> c
    z1(zcount, 1) = <SPAN style="color:darkblue">Not</SPAN> y.HasFormula
    zcount = zcount + 1
<SPAN style="color:darkblue">Next</SPAN> y
IsNotFormula = z1
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Function</SPAN></FONT>

=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))

:confused:
 
Upvote 0
NateO said:
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))

:confused:

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.
 
Upvote 0
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. (y)
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top