Can UDF Be Used in an Array Formula&

masouder

Board Regular
Joined
Jul 5, 2013
Messages
65
I have a formula that calculates the number of unique Accounts for a selected month. The following formula calculates for January, where B3 contains the number 1:
Excel Formula:
{=SUM(--(FREQUENCY(IF(MONTH(DataCreated)=B3,MATCH(DataAccount,DataAccount,0)),ROW(DataAccount)-ROW(Data!D6)+1)>0))}

This formula works perfectly.

I want to calculate the same but by quarter. To do so I created a simple function in VBA to calculate the quarter, titled CalcQuarter, and I created the following formula for Q1:
Excel Formula:
{=SUM(--(FREQUENCY(IF(CalcQuarter(DataCreated)=1,MATCH(DataAccount,DataAccount,0)),ROW(DataAccount)-ROW(Data!D6)+1)>0))}

Unfortunately, this formula returns #VALUE. Note that the only difference between the two formulas is that the first uses the MONTH function and the second uses the CalcQuarter function that I wrote.

Because the formulas are so similar, all I could think of is that the array formula does not like the user defined function.

Any thoughts on why the second formula does not work or how to correct?

Thanks!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,200
Office Version
  1. 2016
Platform
  1. Windows
It is not the array formula but I believe the --( syntax does not compute in VBA :), unless you use VBA to write array formula in the cell
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,533
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
We need more information: a data sample, the UDF you've created along with expected result(s).

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,533
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
You dont need a UDF to get the quarter from a date. You can use
=ROUNDUP(MONTH(date)/3,0)

And your array formula would be something like this
=SUM(--(FREQUENCY(IF(ROUNDUP(MONTH(DataCreated)/3,0)=1,MATCH(DataAccount,DataAccount,0)),ROW(DataAccount)-MIN(ROW(DataAccount))+1)>0))
Ctrl+Shift+Enter

Just out of curiosity, I think it would be possible to use this UDF.
VBA Code:
Function CalcQuarter(r As Range) As Variant
    Dim arr() As Variant, i As Long
   
    ReDim arr(1 To r.Rows.Count)
   
    For i = 1 To r.Rows.Count
        Select Case Month(r.Cells(i))
            Case 1 To 3
                arr(i) = 1
            Case 4 To 6
                arr(i) = 2
            Case 7 To 9
                arr(i) = 3
            Case 10 To 12
                arr(i) = 4
        End Select
    Next i
    CalcQuarter = Application.Transpose(arr)
End Function

And the formula would be
=SUM(--(FREQUENCY(IF(CalcQuarter(DataCreated)=1,MATCH(DataAccount,DataAccount,0)),ROW(DataAccount)-MIN(ROW(DataAccount))+1)>0))
Ctrl+Shift+Enter

M.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,603
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@masouder
I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

masouder

Board Regular
Joined
Jul 5, 2013
Messages
65
Thanks for all of the replies. The
Excel Formula:
ROUNDUP(MONTH(DataCreated)/3,0)=1
suggestion worked perfectly.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,603
Office Version
  1. 365
Platform
  1. Windows
suggestion worked perfectly.
So you are not using Microsoft 365 then?
Would still be a good idea to record your version(s) as suggested then we wouldn't have to wonder every time you asked a question. ;)

For other readers with 365, there is no need for an array-entered formula as this standard-entry formula should do the same job*.

Excel Formula:
=ROWS(UNIQUE(FILTER(DataAccount,CEILING(MONTH(DataCreated),3)=3,"")))

*It would need slight adjustment if it is possible there might be no accounts in the quarter.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,417
Messages
5,642,003
Members
417,250
Latest member
spr1nger

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
Top