Need array formula to ignore hidden values

[NickH]

I have the following array formula dragged across several columns,
which works a treat, but the data may be filtered by various criteria
and there's the problem - It doesn't ignore hidden values.

=SUM((NR_DataType_List=
$AC77)*(INDIRECT(ADDRESS(NRc_TopRow,COLUMN())):INDIRECT(ADDRESS(NRc_BotRow,COLUMN()))))

I've tried modifying it to use SUBTOTAL e.g.

=SUBTOTAL(109,(NR_DataType_List=
$AC77)*(INDIRECT(ADDRESS(NRc_TopRow,COLUMN())):INDIRECT(ADDRESS(NRc_BotRow,COLUMN()))))

....but this just gives me a 'The formula you typed contains an error'
message. Can SUBTOTAL even be used in an array formula? Is there a way
to make this work?

NR_DataType_List is a dynamic named range which sizes itself to a list
of datatypes in column A
$AC77 contains a fixed datatype (other rows to which the formula is
copied contain other datatypes)
NRc_TopRow is a defined name returning the top row of NR_DataType_List
NRc_BotRow is a defined name returning the bottom row of
NR_DataType_List
The COLUMN below the formula, adjacent to NR_DataType_List contains
the numbers that are to be summed.

 

[NickH]

In case it helps anyone else; I've worked out a way round this by
using a UDF...

Public Function IsVisible(ByVal Target As Range)
Dim i As Long
Dim ArrVisible()

ReDim ArrVisible(1 To Target.Rows.Count)
For i = LBound(ArrVisible) To UBound(ArrVisible)
ArrVisible(i) = Not Target.Rows(i).EntireRow.Hidden
Next i
IsVisible = Application.WorksheetFunction.Transpose(ArrVisible)
End Function

The new formula looks like this...
=SUM(IsVisible(NR_DataType_List)*(NR_DataType_List=
$AC77)*(INDIRECT(ADDRESS(NRc_TopRow,COLUMN())):INDIRECT(ADDRESS(NRc_BotRow,COLUMN()))))

However, this does slow calculation down a little so if anyone knows
of a non-UDF solution I'd be glad to see it.

Br, Nick.