# Can UDF Be Used in an Array Formula&

#### masouder

##### Board Regular
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
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
We need more information: a data sample, the UDF you've created along with expected result(s).

M.

#### Marcelo Branco

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

@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
Thanks for all of the replies. The
Excel Formula:
``ROUNDUP(MONTH(DataCreated)/3,0)=1``
suggestion worked perfectly.

#### Marcelo Branco

##### MrExcel MVP
You're welcome. Thanks for the feedback.

M.

#### Peter_SSs

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

Replies
0
Views
84
Replies
2
Views
217
Replies
5
Views
215
Replies
3
Views
109
Replies
2
Views
89

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.

### Which adblocker are you using?

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

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