User defined function inside standard SUMIFS formula-CodePudding

I have written a function that checks cell to be crossed out:

Function isCrossedout(myRange As Range)
    isCrossedout = myRange.Font.Strikethrough
End Function

and I have a column "A:A" of numbers where I want to sum up crossed out elements only.

Can I insert in any cell the standard function SUMIFS with my user defined function? When I try this:

someCell.FormulaR1C1 = "=SUMIFS('Page'!RC1:RC1, isCrossedout)"

it returns zero value when the formula applied.

I realize there is a way to use summing such cells in a pure VBA way with a loop, but I want to try to use it with Excel SUMIFS formula.

CodePudding user response：

First, you need isCrossedout to return a boolean array.

For a version that can handle a multi-column input:

Function isCrossedout(myRange As Range) As Boolean()
    Dim arr() As Boolean
    ReDim arr(1 To myRange.Rows.Count, 1 To myRange.Columns.Count)
    
    Dim i As Long, j As Long
    For i = 1 To myRange.Rows.Count
        For j = 1 To myRange.Columns.Count
            arr(i, j) = myRange.Cells(i, j).Font.Strikethrough
        Next
    Next
    
    isCrossedout = arr
End Function