Excel “Subtotal” array formula - Other form of sum

Question

This is a continuation of the question excel different SUM.IF array function, But since I've marked that as solved, I created a new question.

What I wanted there was a distinct sum of some values, and I have implemented @Marc's solution. However the report requirements have changed. I now need to exclude all values that are hidden, but still keep the original calculation method. Basicly i want to add a feature in the same way a SUBTOTAL(109, ref) would work.

To this I've created a simple VBA function CellIsNotHidden(Range), which returns 0 or 1 depending on the cell.

Therefore my best guess would be a formula like: {=SUM(IF($B1:$B7<>$B2:$B8,D2:D8,0)*CellIsNotHidden(D2:D8))}

But this function doesn't work, because CellIsNotHidden is not an array function.

How can I solve this?

In advance, thanks

Gunnar

Edit:

Thought I should include the simple VBA function:

  Function CellIsNotHidden(InputRange As Range)

  If InputRange.Cells.Height = 0 Then
      CellIsNotHidden = 0
  Else
      If InputRange.Cells.Width = 0 Then
          CellIsNotHidden = 0
      Else
          CellIsNotHidden = 1
      End If
  End If

  End Function

Answer 1

Try this for UDF CellIsNotHidden. This will handle 1d (vector) and 2d arrays. Tested:

Function CellIsNotHidden(MyRange As Range) As Variant
  Dim RootCell As Range
  Dim tmpResult() As Long
  Dim i As Long
  Dim j As Long

On Error GoTo Whoops
  ReDim tmpResult(0 To MyRange.Rows.Count - 1, 0 To MyRange.Columns.Count - 1)
  Set RootCell = MyRange.Cells(1, 1)
  For j = 0 To MyRange.Columns.Count - 1
    For i = 0 To MyRange.Rows.Count - 1
      tmpResult(i, j) = Not (RootCell.Offset(i, j).EntireColumn.hidden Or RootCell.Offset(i, j).EntireRow.hidden)
    Next i
  Next j
  CellIsNotHidden = tmpResult
On Error GoTo 0
  Exit Function
Whoops:
  Debug.Print Err & " " & Error
End Function

Answer 2

Instead of using the UDF CellIsNotHidden(D2:D8) you could also try either of these:

SUBTOTAL(109,OFFSET(D2,ROW(D2:D8)-ROW(D2),))
SUBTOTAL(109,OFFSET(D2:D8,ROW(D2:D8)-MIN(ROW(D2:D8)),,1))