0 votes
in VBA by Skilled (376 points)

This is an Excel Formula question, not a VBA question. Assume a workbook with 3 worksheets named Sheet1, Sheet2, and Sheet3. If I put the value 1 in cell A1 of each sheet, then consider the results of these formulas:
Formula                          ...  Result
=COUNT(Sheet1:Sheet3!A1)         ...  3
=COUNTA(Sheet1:Sheet3!A1)        ...  3
=COUNTIF(Sheet1:Sheet3!A1,">0")  ...  #VALUE!
=COUNTBLANK(Sheet1:Sheet3!A1)    ...  #VALUE!
=SUM(Sheet1:Sheet3!A1)           ...  3
=SUMIF(Sheet1:Sheet3!A1,">0")    ...  #VALUE!

Can anyone explain why the range parameter is treated differently in some of these functions? To make them work as intended, it is necessary to create a list of worksheet names that is referenced using INDIRECT. For example, if cell Z1 is "Sheet1", Z2 is "Sheet2", and Z3 is "Sheet3" then:
Formula                                           ...  Result
=SUMPRODUCT(COUNTIF(INDIRECT(Z1:Z3&"!A1"),">0"))  ...  3
=SUMPRODUCT(COUNTBLANK(INDIRECT(Z1:Z3&"!A1")))    ...  0
=SUMPRODUCT(SUMIF(INDIRECT(Z1:Z3&"!A1"),">0"))    ...  3

Another method is to define a named range MySheets that refers to
={"Sheet1","Sheet2","Sheet3"}
then use MySheets with INDIRECT instead of Z1:Z3. 

Why are these Excel functions apparently inconsistent?

2 Answers

0 votes
by Skilled (376 points)
edited by
 
Best answer

Here is my answer to certain functions that should accommodate 3D ranges but were ignored by Excel developers. This is Part 1 of 2. Part 2 contains only Private Sub ModuleFunctions_Register.

Option Explicit
'
' This module includes code for the following Public Functions (UDFs)
'   COUNTBLANK3D, COUNTIF3D, SUMIF3D, AVERAGEIF3D, MAXIF3D, MINIF3D, plus MAXIF, MINIF,
' plus code for Private Function Func3D to support Public Functions indicated above,
' plus code for Private Sub ModuleFunctions_Register to register the Public Functions indicated above.
' These "3D" UDFs extend Excel's built-in functions to provide support for 3D ranges.
'   see https://bettersolutions.com/excel/formulas/three-dimensional.htm
' MAXIF and MINIF (not built-in by Microsoft) are designed analogous to SUMIF;
' see comments regarding Office 365/2019+ in code for MAXIF and MINIF.
'
' Nov 2019 by J. Woolley
'

Private Function Func3D(Func As String, FirstSheetRange As Range, LastSheetRange As Variant, _
    Optional Criteria As Variant, Optional AltRange As Variant) As Variant
'
' Given FirstSheetRange and LastSheetRange (common contiguous ranges on first and last of
'   sequential worksheets in any single workbook), return Func result for 3D range
'
    Dim oWB As Workbook, sAddress As String, sAltAddress As String
    Dim nFirstSheet As Long, nLastSheet As Long, nStep As Long, n As Long
    Dim vResult As Variant, vM As Variant, bFirst As Boolean
    Dim rRange As Range, rAltRange As Range
    
    On Error GoTo ErrorHandler
    With FirstSheetRange
        sAddress = .Address
        Set oWB = .Parent.Parent
        nFirstSheet = .Parent.Index
    End With
    If IsMissing(LastSheetRange) Then
        nLastSheet = nFirstSheet
    ElseIf TypeName(LastSheetRange) = "Range" Then
        With LastSheetRange
            If (.Parent.Parent Is oWB) Then
                nLastSheet = .Parent.Index
            Else
                GoTo ErrorHandler
            End If
        End With
    Else
        GoTo ErrorHandler
    End If
    If IsMissing(AltRange) Then
        Set rAltRange = FirstSheetRange
    ElseIf TypeName(AltRange) = "Range" Then
        Set rAltRange = AltRange
    Else
        GoTo ErrorHandler
    End If
    sAltAddress = rAltRange.Address
    vResult = 0
    bFirst = True
    nStep = IIf(nLastSheet < nFirstSheet, -1, 1)
    For n = nFirstSheet To nLastSheet Step nStep
        Set rRange = oWB.Worksheets(n).Range(sAddress)
        If nFirstSheet <> nLastSheet Then
            Set rAltRange = oWB.Worksheets(n).Range(sAltAddress)
        End If
        With Application.WorksheetFunction
            Select Case UCase(Func)
            Case "COUNTBLANK"
                vResult = vResult + .COUNTBLANK(rRange)
            Case "COUNTIF"
                vResult = vResult + .CountIf(rRange, Criteria)
            Case "SUMIF"
                vResult = vResult + .SumIf(rRange, Criteria, rAltRange)
            Case "MAXIF"
                vM = MAXIF(rRange, Criteria, rAltRange)
                If bFirst Then
                    vResult = vM
                    bFirst = False
                ElseIf vM > vResult Then
                    vResult = vM
                End If
            Case "MINIF"
                vM = MINIF(rRange, Criteria, rAltRange)
                If bFirst Then
                    vResult = vM
                    bFirst = False
                ElseIf vM < vResult Then
                    vResult = vM
                End If
            Case Else
                GoTo ErrorHandler
            End Select
        End With
    Next n
    Func3D = vResult
    Exit Function
    
ErrorHandler:
    If Err <> 0 Then Debug.Print "Error"; Err; Err.Description
    On Error GoTo 0
    Func3D = CVErr(xlErrValue)
    
End Function

Public Function COUNTBLANK3D(FirstSheetRange As Range, _
    Optional LastSheetRange As Variant) As Variant
'
' COUNTBLANK for 3D range (common contiguous range on sequential worksheets in any single workbook)
' Example: =COUNTBLANK3D('My Sheet1'!A1:C6,'My Sheet3'!A1:C6)
'
    COUNTBLANK3D = Func3D("COUNTBLANK", FirstSheetRange, LastSheetRange)

End Function

Public Function COUNTIF3D(FirstSheetRange As Range, Criteria As Variant, _
    Optional LastSheetRange As Variant) As Variant
'
' COUNTIF for 3D range (common contiguous range on sequential worksheets in any single workbook)
' Example: =COUNTIF3D('My Sheet1'!A1:C6,">=13",'My Sheet3'!A1:C6)
'
    COUNTIF3D = Func3D("COUNTIF", FirstSheetRange, LastSheetRange, Criteria)

End Function

Public Function SUMIF3D(FirstSheetRange As Range, Criteria As Variant, _
    Optional AltRange As Variant, Optional LastSheetRange As Variant) As Variant
'
' SUMIF for 3D ranges (common contiguous ranges on sequential worksheets in any single workbook)
'   Common contiguous ranges are evaluated for each worksheet; therefore, AltRange applies
'   to all worksheets unless LastSheetRange is either omitted or set to FirstSheetRange.
'   AltRange will be adjusted to the same size and shape as FirstSheetRange.
' Example: =SUMIF3D('My Sheet1'!A1:C6,">=13",B4:D9,'My Sheet3'!A1:C6)
'
    SUMIF3D = Func3D("SUMIF", FirstSheetRange, LastSheetRange, Criteria, AltRange)

End Function

Public Function AVERAGEIF3D(FirstSheetRange As Range, Criteria As Variant, _
    Optional AltRange As Variant, Optional LastSheetRange As Variant) As Variant
'
' AVERAGEIF for 3D ranges (common contiguous ranges on sequential worksheets in any single workbook)
'   Common contiguous ranges are evaluated for each worksheet; therefore, AltRange applies
'   to all worksheets unless LastSheetRange is either omitted or set to FirstSheetRange.
'   AltRange will be adjusted to the same size and shape as FirstSheetRange.
' Example: =AVERAGEIF3D('My Sheet1'!A1:C6,">=13",B4:D9,'My Sheet3'!A1:C6)
'
    Dim X As Variant
    
    X = Func3D("COUNTIF", FirstSheetRange, LastSheetRange, Criteria)
    If X = 0 Then
        AVERAGEIF3D = CVErr(xlErrDiv0)
    Else
        AVERAGEIF3D = Func3D("SUMIF", FirstSheetRange, LastSheetRange, Criteria, AltRange) / X
    End If

End Function

Public Function MAXIF3D(FirstSheetRange As Range, Criteria As Variant, _
    Optional AltRange As Variant, Optional LastSheetRange As Variant) As Variant
'
' MAXIF for 3D range (common contiguous range on sequential worksheets in any single workbook)
'   Common contiguous ranges are evaluated for each worksheet; therefore, AltRange applies
'   to all worksheets unless LastSheetRange is either omitted or set to FirstSheetRange.
'   AltRange will be adjusted to the same size and shape as FirstSheetRange.
' Example: =MAXIF3D('My Sheet1'!A1:C6,">=13",B4:D9,'My Sheet3'!A1:C6)
'
    MAXIF3D = Func3D("MAXIF", FirstSheetRange, LastSheetRange, Criteria, AltRange)

End Function

Public Function MINIF3D(FirstSheetRange As Range, Criteria As Variant, _
    Optional AltRange As Variant, Optional LastSheetRange As Variant) As Variant
'
' MINIF for 3D range (common contiguous range on sequential worksheets in any single workbook)
'   Common contiguous ranges are evaluated for each worksheet; therefore, AltRange applies
'   to all worksheets unless LastSheetRange is either omitted or set to FirstSheetRange.
'   AltRange will be adjusted to the same size and shape as FirstSheetRange.
' Example: =MINIF3D('My Sheet1'!A1:C6,">=13",B4:D9,'My Sheet3'!A1:C6)
'
    MINIF3D = Func3D("MINIF", FirstSheetRange, LastSheetRange, Criteria, AltRange)

End Function

Public Function MAXIF(Range As Range, Criteria As Variant, _
    Optional AltRange As Variant) As Variant ' see comments re. Office 365/2019+
'
' MAXIF for 1D ranges (contiguous ranges on any worksheets in any single workbook)
'   AltRange and Range need not be on the same worksheet.
'   AltRange will be adjusted to the same size and shape as Range.
' Example: =MAXIF('My Sheet1'!A1:C6,">=13",'My Sheet3'!B4:D9)
'
    Dim nRows As Long, nCols As Long, rAltRange As Range
    
    nRows = Range.Rows.Count
    nCols = Range.Columns.Count
    If IsMissing(AltRange) Then
        Set rAltRange = Range
    ElseIf TypeName(AltRange) = "Range" Then
        Set rAltRange = AltRange.Resize(nRows, nCols)
    Else
        MAXIF = CVErr(xlErrValue)
        Exit Function
    End If
' The following statement requires Office 365/2019+
    MAXIF = Application.WorksheetFunction.MaxIfs(rAltRange, Range, Criteria)
' If Office 365/2019+ is not available, disable the previous statement and enable the remainder
'    Dim nRow As Long, nCol As Long, vResult As Variant, vM As Variant, bFirst As Boolean
'    bFirst = True
'    With Application.WorksheetFunction
'        For nCol = 1 To nCols
'            For nRow = 1 To nRows
'                If .CountIf(Range.Cells(nRow, nCol), Criteria) > 0 Then
'                    vM = rAltRange.Cells(nRow, nCol)
'                    If .IsNumber(vM) Then
'                        If bFirst Then
'                            vResult = vM
'                            bFirst = False
'                        ElseIf vM > vResult Then
'                            vResult = vM
'                        End If
'                    End If
'                End If
'            Next nRow
'        Next nCol
'    End With
'    MAXIF = vResult

End Function

Public Function MINIF(Range As Range, Criteria As Variant, _
    Optional AltRange As Variant) As Variant ' see comments re. Office 365/2019+
'
' MINIF for 1D ranges (contiguous ranges on any worksheets in any single workbook)
'   AltRange and Range need not be on the same worksheet.
'   AltRange will be adjusted to the same size and shape as Range.
' Example: =MINIF('My Sheet1'!A1:C6,">=13",'My Sheet3'!B4:D9)
'
    Dim nRows As Long, nCols As Long, rAltRange As Range
    
    nRows = Range.Rows.Count
    nCols = Range.Columns.Count
    If IsMissing(AltRange) Then
        Set rAltRange = Range
    ElseIf TypeName(AltRange) = "Range" Then
        Set rAltRange = AltRange.Resize(nRows, nCols)
    Else
        MINIF = CVErr(xlErrValue)
        Exit Function
    End If
' The following statement requires Office 365/2019+
    MINIF = Application.WorksheetFunction.MinIfs(rAltRange, Range, Criteria)
' If Office 365/2019+ is not available, disable the previous statement and enable the remainder
'    Dim nRow As Long, nCol As Long, vResult As Variant, vM As Variant, bFirst As Boolean
'    bFirst = True
'    With Application.WorksheetFunction
'        For nCol = 1 To nCols
'            For nRow = 1 To nRows
'                If .CountIf(Range.Cells(nRow, nCol), Criteria) > 0 Then
'                    vM = rAltRange.Cells(nRow, nCol)
'                    If .IsNumber(vM) Then
'                        If bFirst Then
'                            vResult = vM
'                            bFirst = False
'                        ElseIf vM < vResult Then
'                            vResult = vM
'                        End If
'                    End If
'                End If
'            Next nRow
'        Next nCol
'    End With
'    MINIF = vResult

End Function

 

by Skilled (376 points)

This is Part 2 of 2. Part 1 contains everything except Private Sub ModuleFunctions_Register.

Private Sub ModuleFunctions_Register()
'
' Private Sub to register Public Functions in this module
' Run this once manually (F5), but not while enabled as Add-In; repeat if there are changes
'
    Dim sName As String, sDesc As String
    Dim sArgDesc() As String
    Dim msg, ans
    
    Const nCNbr As Integer = 14 ' category name is "User Defined"
    
    sName = "COUNTBLANK3D"
    sDesc = "Count empty cells for a 3D range (matching range, sequential worksheets, single workbook)."
    ReDim sArgDesc(1 To 2)
    sArgDesc(1) = "Range of cells on First worksheet to determine if empty; applies to all sheets"
    sArgDesc(2) = "Range on Last worksheet (optional); will be adjusted to match First sheet's range; result is same as COUNTBLANK if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

    sName = "COUNTIF3D"
    sDesc = "Count cells that meet Criteria for a 3D range (matching range, sequential worksheets, single workbook)."
    ReDim sArgDesc(1 To 3)
    sArgDesc(1) = "Range of cells on First worksheet to compare with Criteria; applies to all sheets"
    sArgDesc(2) = "Number, expression, cell reference, or text that determines which cells to count"
    sArgDesc(3) = "Range on Last worksheet (optional); will be adjusted to match First sheet's range; result is same as COUNTIF if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

    sName = "SUMIF3D"
    sDesc = "Sum values based on Criteria for a 3D range (matching ranges, sequential worksheets, single workbook)."
    ReDim sArgDesc(1 To 4)
    sArgDesc(1) = "Range of cells on First worksheet to compare with Criteria; applies to all sheets"
    sArgDesc(2) = "Number, expression, cell reference, or text that determines which cells to sum"
    sArgDesc(3) = "Range of cells to sum (optional); will be adjusted to size and shape of First sheet's range; applies to all sheets unless Last=First; same as FirstSheetRange if omitted"
    sArgDesc(4) = "Range on Last worksheet (optional); will be adjusted to match First sheet's range; result is same as SUMIF if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

    sName = "AVERAGEIF3D"
    sDesc = "Average values based on Criteria for a 3D range (matching ranges, sequential worksheets, single workbook)."
    ReDim sArgDesc(1 To 4)
    sArgDesc(1) = "Range of cells on First worksheet to compare with Criteria; applies to all sheets"
    sArgDesc(2) = "Number, expression, cell reference, or text that determines which cells to average"
    sArgDesc(3) = "Range of cells to average (optional); will be adjusted to size and shape of First sheet's range; applies to all sheets unless Last=First; same as FirstSheetRange if omitted"
    sArgDesc(4) = "Range on Last worksheet (optional); will be adjusted to match First sheet's range; result is same as AVERAGEIF if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

    sName = "MAXIF3D"
    sDesc = "Maximum value based on Criteria for a 3D range (matching ranges, sequential worksheets, single workbook)."
    ReDim sArgDesc(1 To 4)
    sArgDesc(1) = "Range of cells on First worksheet to compare with Criteria; applies to all sheets"
    sArgDesc(2) = "Number, expression, cell reference, or text that determines which cells to scan for maximum value"
    sArgDesc(3) = "Range of cells to scan for maximum value (optional); will be adjusted to size and shape of First sheet's range; applies to all sheets unless Last=First; same as FirstSheetRange if omitted"
    sArgDesc(4) = "Range on Last worksheet (optional); will be adjusted to match First sheet's range; result is same as MAXIF if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

    sName = "MINIF3D"
    sDesc = "Minimum value based on Criteria for a 3D range (matching ranges, sequential worksheets, single workbook)."
    ReDim sArgDesc(1 To 4)
    sArgDesc(1) = "Range of cells on First worksheet to compare with Criteria; applies to all sheets"
    sArgDesc(2) = "Number, expression, cell reference, or text that determines which cells to scan for minimum value"
    sArgDesc(3) = "Range of cells to scan for minimum value (optional); will be adjusted to size and shape of First sheet's range; applies to all sheets unless Last=First; same as FirstSheetRange if omitted"
    sArgDesc(4) = "Range on Last worksheet (optional); will be adjusted to match First sheet's range; result is same as MINIF if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

    sName = "MAXIF"
    sDesc = "Maximum value based on Criteria for a range."
    ReDim sArgDesc(1 To 3)
    sArgDesc(1) = "Range of cells to compare with Criteria"
    sArgDesc(2) = "Number, expression, cell reference, or text that determines which cells to scan for maximum value"
    sArgDesc(3) = "Range of cells to scan for maximum value (optional); will be adjusted to size and shape of Range; same as Range if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

    sName = "MINIF"
    sDesc = "Minimum value based on Criteria for a range."
    ReDim sArgDesc(1 To 3)
    sArgDesc(1) = "Range of cells to compare with Criteria"
    sArgDesc(2) = "Number, expression, cell reference, or text that determines which cells to scan for minimum value"
    sArgDesc(3) = "Range of cells to scan for minimum value (optional); will be adjusted to size and shape of Range; same as Range if omitted"
    msg = "Do you want to register Function " + sName + "?" + vbNewLine + "(This only needs to be done once, unless there are changes.)"
    ans = MsgBox(msg, (vbYesNo + vbQuestion), "Register Function")
    If ans = vbYes Then Application.MacroOptions Macro:=sName, Description:=sDesc, Category:=nCNbr, ArgumentDescriptions:=sArgDesc

End Sub

 

0 votes
by Beginner (4 points)
Not all functions in Excel use 3d rages.

This link has a list of these functions

https://bettersolutions.com/excel/formulas/three-dimensional.htm
by Skilled (376 points)
Thanks for the reference, but it seems like the developers simply forgot to accommodate 3D ranges in COUNTIF, COUNTBLANK, SUMIF, and similar statistical functions.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...