Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Cells When Spread Sheet is Filtered | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
paste over a filtered range | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
counting cells in a range | Excel Discussion (Misc queries) |