Convert array formula to UDF

[YasserKhalil]

Hello everyone
I have this array formula

=SUM(IF(($B$3:$B$17=B3)*(E3<$E$3:$E$17),1/COUNTIFS($B$3:$B$17,B3,$E$3:$E$17,$E$3:$E$17)))+1

And I would like to convert that formula to UDF (user-defined function)

Can you help me please?

 

[Chihiro]

Why UDF?

It will likely be slower than the array formula, and more prone to error.

Is there a specific reason, other than academic curiosity?

If you have Excel 2016. I'd suggest loading data to data model and using DAX.

See link for how to use DAX without PowerPivot.
http://quadexcel.com/excel-magic-tr...-pivot-dax-measures-relationships-data-model/

 

[YasserKhalil]

Thank you very much for reply
It is just for academic curiosity and I never mind converting to VBA code .. if the UDF will be more prone to error

 

[Marc L]

Hi !

Use FormulaArray property or Evaluate method …​

 

[YasserKhalil]

Thanks a lot Mr. Marc
How can add the letter "R" to the duplicates in results skipping the first instance

 

[Marc L]

Via an IF within formula, try first in a free column …​

 

[YasserKhalil]

I tried that code but not correct results

Sub Test()
    '=SUM(IF(($B$3:$B$17=B3)*(E3<$E$3:$E$17),1/COUNTIFS($B$3:$B$17,B3,$E$3:$E$17,$E$3:$E$17)))+1
    With Range("H3:H" & Cells(Rows.Count, 2).End(xlUp).Row)
        .FormulaArray = "=SUM(IF(($B$3:$B$17=B3)*(E3<$E$3:$E$17),1/COUNTIFS($B$3:$B$17,B3,$E$3:$E$17,$E$3:$E$17)))+1"
    End With
End Sub

Also tried Evaluate and the same incorrect results

 

[NARAYANK991]

Hi ,

Try this :

Sub Test()
    '=SUM(IF(($B$3:$B$17=B3)*(E3<$E$3:$E$17),1/COUNTIFS($B$3:$B$17,B3,$E$3:$E$17,$E$3:$E$17)))+1
    [H3].FormulaArray = "=SUM(IF(($B$3:$B$17=B3)*(E3<$E$3:$E$17),1/COUNTIFS($B$3:$B$17,B3,$E$3:$E$17,$E$3:$E$17)))+1  & IF(COUNTIFS(B$3:B3,B3,E$3:E3,E3) = 1, """", ""R"")"
    Range("H3:H" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown
End Sub

Narayan

 

[YasserKhalil]

Thank you very much for this great solution ..
Best Regards