title: Excel "Subtotal" array formula - Other form of sum.if
body: <p>This is a continuation of the question <a href="https://stackoverflow.com/questions/10483048/excel-different-sum-if-array-function">excel different SUM.IF array function</a>, But since I've marked that as solved, I created a new question.</p>
  
  <p>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.</p>
  
  <p>To this I've created a simple VBA function CellIsNotHidden(Range), which returns 0 or 1 depending on the cell.</p>
  
  <p>Therefore my best guess would be a formula like: <code>{=SUM(IF($B1:$B7&lt;&gt;$B2:$B8,D2:D8,0)*CellIsNotHidden(D2:D8))}</code></p>
  
  <p>But this function doesn't work, because CellIsNotHidden is not an array function.</p>
  
  <p>How can I solve this?</p>
  
  <p>In advance, thanks</p>
  
  <p>Gunnar</p>
  
  <p>Edit:</p>
  
  <p>Thought I should include the simple VBA function:</p>
  
  <pre><code>  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
  </code></pre>