Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF Function on filtered data
I want to be able to filter data (using criteria in Col A) and then count
numbers (in another column) that are greater than or less than a certain number. I only want to count the numbers from the filtered area, not from the whole range of data. I want this to be dynamic, so that when I change filters in Col A it still only counts numbers from the filtered data. The COUNTIF function counts numbers from the whole range and the SUBTOTAL function doesn't have a reference number for the COUNTIF function. Is there a way to do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF Function on filtered data
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$19)-ROW($A$1),,1)),--($C$2:$C$1920)),--($C$2:$C$19<=50))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TG" wrote in message ... I want to be able to filter data (using criteria in Col A) and then count numbers (in another column) that are greater than or less than a certain number. I only want to count the numbers from the filtered area, not from the whole range of data. I want this to be dynamic, so that when I change filters in Col A it still only counts numbers from the filtered data. The COUNTIF function counts numbers from the whole range and the SUBTOTAL function doesn't have a reference number for the COUNTIF function. Is there a way to do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF Function on filtered data
thank you so much Bob - I've been working on this for hours, that's so
amazingly helpful!!! "Bob Phillips" wrote: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$19)-ROW($A$1),,1)),--($C$2:$C$1920)),--($C$2:$C$19<=50)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "TG" wrote in message ... I want to be able to filter data (using criteria in Col A) and then count numbers (in another column) that are greater than or less than a certain number. I only want to count the numbers from the filtered area, not from the whole range of data. I want this to be dynamic, so that when I change filters in Col A it still only counts numbers from the filtered data. The COUNTIF function counts numbers from the whole range and the SUBTOTAL function doesn't have a reference number for the COUNTIF function. Is there a way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif only visible cells (filtered) | Excel Worksheet Functions | |||
Using Data Fill function to copy a Countif formula | Excel Worksheet Functions | |||
Using Data Fill function to copy a Countif formula | Excel Worksheet Functions | |||
Subtotal function with Filtered Data | Excel Discussion (Misc queries) | |||
analysing data from alternate columns using the countif function | Excel Discussion (Misc queries) |