Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 60
Default Need array formula to ignore hidden values

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())):IND IRECT(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())):IND IRECT(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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 60
Default Need array formula to ignore hidden values

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())):IND IRECT(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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I want a formula to ignore text values in cell references Russellrupert New Users to Excel 3 January 11th 12 11:15 PM
Ignore Hidden Rows and Count of distinct values Anuma (GGK Tech) Excel Worksheet Functions 3 April 13th 10 02:00 AM
How to ignore hidden columns in an "Average" formula? DotK Excel Discussion (Misc queries) 8 January 1st 10 02:49 PM
drag data vertically over hidden cells.. ignore hidden cells Derwood Excel Discussion (Misc queries) 1 March 6th 08 11:59 PM
How do I ignore values while copying from hidden rows? Mayank Excel Worksheet Functions 1 July 19th 05 02:48 PM


All times are GMT +1. The time now is 08:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"