Got it, let's break down why your current formula isn't working and walk through practical fixes.

The Root Problem #

When you write =SUMIF(A1:E1,Checkthis(N)=23), Excel evaluates Checkthis(N)=23 first—this gives a single TRUE/FALSE value. SUMIF then tries to match cells in A1:E1 to that fixed boolean, which isn't what you want. Your goal is to run CheckThis on each cell in A1:E1, check if the result equals 23, then sum the matching cells.

Solution 1: Use SUMPRODUCT (Works in All Excel Versions) #

This is the most reliable approach for older and newer Excel versions. Use this formula:

=SUMPRODUCT(--(CheckThis(A1:E1)=23), A1:E1)

Let's break it down:

• CheckThis(A1:E1): If your VBA function supports array inputs (we'll cover that if it doesn't), this returns an array of results for each cell in A1:E1.
• --(...): Converts the TRUE/FALSE results of CheckThis(...) = 23 into 1s and 0s.
• SUMPRODUCT multiplies the 1/0 array with your target values (A1:E1) and sums the products—effectively only summing values where the condition is met.

If your sum range is different from the check range (e.g., sum B1:F1 when A1:E1 meets the condition), adjust the formula:

=SUMPRODUCT(--(CheckThis(A1:E1)=23), B1:F1)

Solution 2: Excel 365/2021 Simplified with FILTER or SUM+IF #

If you're on a modern Excel version, you can use more intuitive functions:

• With FILTER:

  =SUM(FILTER(A1:E1, CheckThis(A1:E1)=23))
  

  FILTER first grabs all cells in A1:E1 where CheckThis returns 23, then SUM adds those values.
• With SUM+IF:

  =SUM(IF(CheckThis(A1:E1)=23, A1:E1, 0))
  

  This works as a dynamic array formula (no need for Ctrl+Shift+Enter in Excel 365).

Fixing Your VBA Function for Array Inputs #

If your current CheckThis function only handles single cells, it won't return an array when you pass A1:E1. Update it to support ranges:

Function CheckThis(rng As Range) As Variant
    Dim resultArr() As Variant
    Dim cell As Range
    Dim idx As Integer
    
    ' Initialize array to match the range size
    ReDim resultArr(1 To rng.Cells.Count)
    
    idx = 1
    For Each cell In rng
        ' Replace this line with your actual CheckThis logic
        ' Example: Extract integer from cell value
        resultArr(idx) = CInt(cell.Value)
        idx = idx + 1
    Next cell
    
    ' Return single value if only one cell, else return array
    If rng.Cells.Count = 1 Then
        CheckThis = resultArr(1)
    Else
        CheckThis = resultArr
    End If
End Function

Now your function will correctly return an array when given a range, making all the above formulas work.

内容的提问来源于stack exchange，提问作者Fredi