Using UDF with SUMPRODUCT

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
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?
 

Some videos you may like

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
Joined
Dec 9, 2003
Messages
10,881
Office Version
365, 2010
Platform
Windows, Mobile, Web
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,188
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
Joined
Feb 17, 2002
Messages
9,700
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:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
Windows
Thank you very much!! :biggrin:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,188
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.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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)
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top