SUMIF function ignoring hidden cells

On this page I show how to make a worksheet function like SUMIF, but one that excludes hidden cells.

You can't make Excel's SUMIF function ignore hidden cells. There is a workaround using SUMPRODUCT, SUBTOTAL and OFFSET, but the formula gets pretty complicated, and it is much easier to use your own worksheet function written in VBA.

Here is how I did it, when a reader asked for help. It isn't fast as lightning, if many cells are involved, but it works and is straight forward to use.

To copy the code just highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module.

When you have inserted the code, the dialogue will look like this (in the Danish version of Excel 2003), when you select "Insert Function," user defined category and the function SUMIFHIDDEN:

If Operator is left blank, the function will assume "=". Of course the function can also be typed into a cell.

In Excel's SUMIF function operator and criteria are in one expression, but I have separated the two to make the function faster.

Hidden cells can be in the SumRange, but are ignored. Hidden cells in the criteria range are not ignored, but that can easily be arranged.

The criteria can be a number or text. By declaring it as a String (datatype) the user can type WORD instead of "WORD". If it was declared as a Variant, and the user typed WORD without quotation marks, it would trigger a Type mismatch error.

So the criteria is read into the variable vCheck of the datatype Variant, and if it is numeric, we convert it to a number.

Function SUMIFHIDDEN(CriteriaRange As Range,SumRange As Range, _
Operator As String, Criteria As String) As Double
'Excels SUMIF function includes hidden cells in the sum.
'This function, SumIfHidden, doesn't.

Dim bHidden As Boolean     'True if a cell is hidden
Dim bNoGo As Boolean       'True if criteria is not met
Dim lCount As Long         'Counter
Dim dSum As Double         'Result
Dim rCell As Range         'Range variable
Dim vCheck as variant      'Internal criteria

On Error GoTo ErrorHandle

'Remove any spaces before and after
Operator = Trim$(Operator)

If InStr(1, Operator, " ") Then
   MsgBox "Space not allowed in operator."
   Exit Function
End If

If Len(Operator) = 0 Then Operator = "="

'The input criteria was declared as a string so
'non numeric input (like a word) would be
'accepted without "". So if it is numeric,
'we convert it to a number.
If IsNumeric(Criteria) Then
   vCheck = CDbl(Criteria)
Else
   vCheck = Criteria
End If

'Loop through the cells in the SumRange
For Each rCell In SumRange
   'Reset flags
   bHidden = False
   bNoGo = False
   'Add 1 to counter
   lCount = lCount + 1
   With rCell
      'If the cell is in a hidden row or column, set bHidden = True
      If Rows(.Row).Hidden = True Or _ 
         Columns(.Column).Hidden = True Then bHidden = True
   End With
   'If the cell isn't hidden
   If bHidden = False Then
      'If the counter value isn't bigger than the number of cells
      'in CriteriaRange, we check if the cell meets the criteria.
      'If it doesn't, bNoGo is set to True.
      If lCount <= CriteriaRange.Count Then
         With CriteriaRange.Item(lCount)
            Select Case Operator
               Case "="
                  If .Value <> vCheck Then bNoGo = True
               Case ">"
                  If .Value <= vCheck Then bNoGo = True
               Case "<"
                  If .Value >= vCheck Then bNoGo = True
               Case ">=", "=>"
                  If .Value < vCheck Then bNoGo = True
               Case "=<", "<="
                  If .Value > vCheck Then bNoGo = True
               Case "<>"
                  If .Value = vCheck Then bNoGo = True
            End Select
         End With
      End If
   End If
   'If the condition was met and the cell isn't hidden,
   'we add the sum-cell value to the old sum.
   If bNoGo = False And bHidden = False Then dSum = dSum + rCell.Value
Next

SUMIFHIDDEN = dSum

BeforeExit:
Set rCell = Nothing
Exit Function
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Function

You can add a short description that will show up in the insert function dialogue. On the picture above I added: "Like Excel's SumIf" etc.

You do that by selecting "Macros" (or ALT+F8) and a list of your macros will appear (if any), but not the function! Write or paste the functions name and click Settings. This will allow a short description.

Remember, that a worksheet function can only return a value when called from a cell formula. It cannot change anything else like e.g. the colour of a range.