Can UDF Be Used in an Array Formula&

[masouder]

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:

{=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:

{=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!

 

[Zot]

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]

We need more information: a data sample, the UDF you've created along with expected result(s).

M.

 

[Marcelo Branco]

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.

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]

@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]

Thanks for all of the replies. The

ROUNDUP(MONTH(DataCreated)/3,0)=1

suggestion worked perfectly.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.

 

[Peter_SSs]

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*.

=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.