非活动工作表中的SumIf并存储为数组元素

SumIf(range, criteria, [sum_range])

Sub SumIfToArray()
    Dim arr() As Variant
    Dim rng As Range
    Dim sumRange As Range
    Dim criteria As Variant
    Dim i As Integer
    
    ' 设置范围和条件
    Set rng = Worksheets("Sheet1").Range("A1:A10")
    Set sumRange = Worksheets("Sheet1").Range("B1:B10")
    criteria = "Apple"
    
    ' 遍历范围，判断条件并计算求和
    ReDim arr(1 To rng.Rows.Count)
    For i = 1 To rng.Rows.Count
        If rng.Cells(i).Value = criteria Then
            arr(i) = sumRange.Cells(i).Value
        End If
    Next i
    
    ' 输出结果
    For i = 1 To rng.Rows.Count
        If Not IsEmpty(arr(i)) Then
            Debug.Print arr(i)
        End If
    Next i
End Sub