# Using UDF with SUMPRODUCT

#### Scott Huish

##### MrExcel MVP
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Zack Barresse

##### MrExcel MVP
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.

##### MrExcel MVP
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.

#### NateO

##### Legend
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))

#### Scott Huish

##### MrExcel MVP
Thank you very much!!

#### NateO

You are welcome.

##### MrExcel MVP
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))

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.

#### NateO

##### Legend

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.

1,102,893
Messages
5,489,561
Members
407,700
Latest member
SimpleJuan

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...