insert UDF in Pivot calculated field

[Afarag]

Hi,

Please i ask for help that i have a VBA that let me count the interior cell colors as below:

Function sum_color(myRange As Range, myColor As Range, Optional myType = 0) As Double
        Dim summ As Double
        Dim clr As Long
        clr = myColor.Interior.Color
       
        summ = 0
        For Each c In myRange
            If clr = c.Interior.Color Then
                If myType = 1 Then
                  summ = summ + c.Text
                ElseIf myType = 0 Then
                  summ = summ + 1
                End If
            End If
        Next
        sum_color = summ
End Function

i want to use this UDF in counting at Pivot.

this VBA teems:

to count: "=sum_color(range,criteria,0)"

to sum: "=sum_color(range,criteria,1)"

i have some colored filled cells, to get outcome I use this function

=IFERROR(sum_color($I5:$BP5,$A$2,1)/(sum_color($I5:$BP5,$A$2,0)*15),"")

to get some total count, I cant use average or averageif/s, its outcome are different when use UDF for all the total range

can i add this UDF in Pivot calculated field.

Thanks a lot,

 

[Afarag]

Dears,

thanks in advance, i'm sure you will help, I get it answered in avoiding using UDF in pivot, i used 2 helper columns 1: to sum, 2: to count interior cell colors, then using calculated field "=Sum /(Count *15)"

partly it's a simple solution but get request handled.

Thanks,