Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jeff
 
Posts: n/a
Default Counting within a filtered range

Hi,

I have found out how to determine the number of items in a filtered range by
using the formula
=SUBTOTAL(2,A5:A3000). This works well and tells me the population "n" of
the data class being analysed.

I now need to find the total number of occurrences within the filtered set.

E.g. for finding how many times 5 appears in the filtered range

5
7
5
8
3
= 2 (i.e. 5 appears twice)

All attempts have yielded the count of occurrences of the unfiltered data or
a formula error.

Is it possible to achieve my objective? Any help greatfully appreciated,

regards

Jeff




  #2   Report Post  
Domenic
 
Posts: n/a
Default

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A3000,ROW(A5:A300 0)-MIN(ROW(A5:A3000)),0
,1)),--(A5:A3000=5))

Hope this helps!

In article , "Jeff"
wrote:

Hi,

I have found out how to determine the number of items in a filtered range by
using the formula
=SUBTOTAL(2,A5:A3000). This works well and tells me the population "n" of
the data class being analysed.

I now need to find the total number of occurrences within the filtered set.

E.g. for finding how many times 5 appears in the filtered range

5
7
5
8
3
= 2 (i.e. 5 appears twice)

All attempts have yielded the count of occurrences of the unfiltered data or
a formula error.

Is it possible to achieve my objective? Any help greatfully appreciated,

regards

Jeff

  #3   Report Post  
Jeff
 
Posts: n/a
Default

This worked beautifully. Many thanks.


"Domenic" wrote in message
...
Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A3000,ROW(A5:A300 0)-MIN(ROW(A5:A3000)),0
,1)),--(A5:A3000=5))

Hope this helps!

In article , "Jeff"
wrote:

Hi,

I have found out how to determine the number of items in a filtered range
by
using the formula
=SUBTOTAL(2,A5:A3000). This works well and tells me the population "n"
of
the data class being analysed.

I now need to find the total number of occurrences within the filtered
set.

E.g. for finding how many times 5 appears in the filtered range

5
7
5
8
3
= 2 (i.e. 5 appears twice)

All attempts have yielded the count of occurrences of the unfiltered data
or
a formula error.

Is it possible to achieve my objective? Any help greatfully appreciated,

regards

Jeff



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
Counting Unique Cells When Spread Sheet is Filtered carl Excel Worksheet Functions 1 June 3rd 05 07:20 PM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 2 May 2nd 05 08:53 AM
paste over a filtered range freddie2711 Excel Discussion (Misc queries) 8 April 27th 05 01:20 PM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 0 April 7th 05 12:47 AM
counting cells in a range Charles Excel Discussion (Misc queries) 5 February 19th 05 12:59 AM


All times are GMT +1. The time now is 04:43 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"