# 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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### 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
80
Replies
2
Views
212
Replies
5
Views
202
Replies
3
Views
97
Replies
2
Views
88

### Forum statistics

1,129,385
Messages
5,636,001
Members
416,891
Latest member
Okomomo ### 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