If you REALLY have a large range of cells to check, and not just one cell
like your sample, you will need to add some functionality to your sheets.
For a formula like this one to work:
=SUMIF3D2("Sheet1:Sheet3!B1:B20","yes","Sheet1:Sheet3!A1:A20")
Add the following two functions into a standard module:
===========
Function SumIf3D2(Range3D As String, Criteria As String, _
Optional Sum_Range As String) As Variant
Dim Sum As Double
Dim vaRng1 As Variant, vaRng2 As Variant
Dim i As Long
Application.Volatile
If Len(Sum_Range) = 0 Then
Sum_Range = Range3D
End If
vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D)
vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, Sum_Range)
Sum = 0
For i = LBound(vaRng1) To UBound(vaRng1)
Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i), Criteria,
vaRng2(i))
Next i
SumIf3D2 = Sum
End Function
Function Parse3DRange2(wb As Workbook, _
SheetsAndRange As String) As Variant
Dim sTemp As String
Dim i As Long, j As Long
Dim Sheet1 As String, Sheet2 As String
Dim aRange() As Range
Dim sRange As String
Dim lFirstSht As Long, lLastSht As Long
Dim rCell As Range
Dim rTemp As Range
On Error GoTo Parse3DRangeError
sTemp = SheetsAndRange
'if it's 3d, rtemp will be nothing
On Error Resume Next
Set rTemp = Range(sTemp)
On Error GoTo Parse3DRangeError
'if it's 3d, parse it
If rTemp Is Nothing Then
i = InStr(sTemp, "!")
If i = 0 Then Err.Raise 9999
'next line will generate an error if range is invalid
'if it's OK, it will be converted to absolute form
sRange = Range(Mid$(sTemp, i + 1)).Address
sTemp = Left$(sTemp, i - 1)
i = InStr(sTemp, ":")
Sheet2 = Trim(Mid$(sTemp, i + 1))
If i > 0 Then
Sheet1 = Trim(Left$(sTemp, i - 1))
Else
Sheet1 = Sheet2
End If
'next lines will generate errors if sheet names are invalid
With wb
lFirstSht = .Worksheets(Sheet1).Index
lLastSht = .Worksheets(Sheet2).Index
'swap if out of order
If lFirstSht > lLastSht Then
i = lFirstSht
lFirstSht = lLastSht
lLastSht = i
End If
'load each cell into an array
j = 0
For i = lFirstSht To lLastSht
For Each rCell In .Sheets(i).Range(sRange)
ReDim Preserve aRange(0 To j)
Set aRange(j) = rCell
j = j + 1
Next rCell
Next i
End With
Parse3DRange2 = aRange
Else
'range isn't 3d, so just load each cell into array
For Each rCell In rTemp.Cells
ReDim Preserve aRange(0 To j)
Set aRange(j) = rCell
j = j + 1
Next rCell
Parse3DRange2 = aRange
End If
Parse3DRangeError:
On Error GoTo 0
Exit Function
End Function 'Parse3DRange
===========
Source:
http://www.dailydoseofexcel.com/archives/2006/02/25/3d-user-defined-functions/
Note, all of the parameters in that formula are strings, surrounded in
quotes. Watch for that.
Is this something you can use?
