User defined function inside standard SUMIFS formula

Question

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.

user_9245853 · Answer

First, you need isCrossedout to return a boolean array. Font.Strikethrough will not return an array, so use a loop:

Function isCrossedout(ByVal myRange As Range) As Boolean()
    Dim arr() As Boolean
    ReDim arr(1 To myRange.Rows.Count, 1) ' assumes myRange has one column
    
    Dim cell As Range
    For Each cell In myRange
        Dim counter As Long
        counter = counter + 1
        
        arr(counter, 1) = cell.Font.Strikethrough
    Next
    
    isCrossedout = arr
End Function

Next, I'd use SUMPRODUCT, with the double unary -- to coerce the boolean array to an array of ones and zeros.

=SUMPRODUCT(A2:A5*--isCrossedout(A2:A5))

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

User defined function inside standard SUMIFS formula

1 Answers