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:
 CriteriaRange are the cells we test for meeting criteria
 SumRange is the range (including hidden cells) used for the sum
 Operator is =, >, <, >=, =< or <>
 Criteria is either a number or a word
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.
Here we go!
Function SUMIFHIDDEN(CriteriaRange As Range,SumRange As Range, _
Operator As String, Criteria As String) As Double
Dim bHidden As Boolean
Dim bNoGo As Boolean
Dim lCount As Long
Dim dSum As Double
Dim rCell As Range
Dim vCheck as variant
On Error GoTo ErrorHandle
Operator = Trim$(Operator)
If InStr(1, Operator, " ") Then
MsgBox "Space not allowed in operator."
Exit Function
End If
If Len(Operator) = 0 Then Operator = "="
If IsNumeric(Criteria) Then
vCheck = CDbl(Criteria)
Else
vCheck = Criteria
End If
For Each rCell In SumRange
bHidden = False
bNoGo = False
lCount = lCount + 1
With rCell
If Rows(.Row).Hidden = True Or _
Columns(.Column).Hidden = True Then bHidden = True
End With
If bHidden = False Then
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 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.
To top
Related:
