Using the "Count" function with filters?
 

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

Using the "Count" function with filters?
 

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson

Using the "Count" function with filters?
 

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson

Using the "Count" function with filters?
 

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

I understand what you suggested, however, it doesn't address the issue of the
result changing when applying filters to other columns in the spreadsheet.

I was able to get the total number of cells that contains, for instance
"DP*", however when I applied filters to another column the total number of
cells containing DP did not change.

"Dave Peterson" wrote:

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

So you want to do the equivalent of =countif() but with filtered data?

If that's close to what you mean:

Aladin Akyurek posted this:

If you're trying to count the occurrences of a certain text in V which
is part of an AutoFiltered range....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),
--(Vrange="Rome"))

would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.

===
that formula sits in one cell. And if you wanted to count the number of Rome's
that appear in B2:B99 after you filter on some other column (mixture of Rome,
Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace
Vrange with B2:B99 in that formula.

Irv wrote:

I understand what you suggested, however, it doesn't address the issue of the
result changing when applying filters to other columns in the spreadsheet.

I was able to get the total number of cells that contains, for instance
"DP*", however when I applied filters to another column the total number of
cells containing DP did not change.

"Dave Peterson" wrote:

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

I tried Aladin's suggested formula, but it did not return any result:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2:F11119,ROW(F2:F11 119)-MIN(ROW(F2:F11119)),,1)),--(F2:F11119="DP*"))



"Dave Peterson" wrote:

So you want to do the equivalent of =countif() but with filtered data?

If that's close to what you mean:

Aladin Akyurek posted this:

If you're trying to count the occurrences of a certain text in V which
is part of an AutoFiltered range....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),
--(Vrange="Rome"))

would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.

===
that formula sits in one cell. And if you wanted to count the number of Rome's
that appear in B2:B99 after you filter on some other column (mixture of Rome,
Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace
Vrange with B2:B99 in that formula.

Irv wrote:

I understand what you suggested, however, it doesn't address the issue of the
result changing when applying filters to other columns in the spreadsheet.

I was able to get the total number of cells that contains, for instance
"DP*", however when I applied filters to another column the total number of
cells containing DP did not change.

"Dave Peterson" wrote:

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

Sorry.

I don't have any more guesses.

Irv wrote:

I tried Aladin's suggested formula, but it did not return any result:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2:F11119,ROW(F2:F11 119)-MIN(ROW(F2:F11119)),,1)),--(F2:F11119="DP*"))

"Dave Peterson" wrote:

So you want to do the equivalent of =countif() but with filtered data?

If that's close to what you mean:

Aladin Akyurek posted this:

If you're trying to count the occurrences of a certain text in V which
is part of an AutoFiltered range....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),
--(Vrange="Rome"))

would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.

===
that formula sits in one cell. And if you wanted to count the number of Rome's
that appear in B2:B99 after you filter on some other column (mixture of Rome,
Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace
Vrange with B2:B99 in that formula.

Irv wrote:

I understand what you suggested, however, it doesn't address the issue of the
result changing when applying filters to other columns in the spreadsheet.

I was able to get the total number of cells that contains, for instance
"DP*", however when I applied filters to another column the total number of
cells containing DP did not change.

"Dave Peterson" wrote:

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

Well, Thanks for your assistance.

"Dave Peterson" wrote:

Sorry.

I don't have any more guesses.

Irv wrote:

I tried Aladin's suggested formula, but it did not return any result:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2:F11119,ROW(F2:F11 119)-MIN(ROW(F2:F11119)),,1)),--(F2:F11119="DP*"))

"Dave Peterson" wrote:

So you want to do the equivalent of =countif() but with filtered data?

If that's close to what you mean:

Aladin Akyurek posted this:

If you're trying to count the occurrences of a certain text in V which
is part of an AutoFiltered range....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),
--(Vrange="Rome"))

would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.

===
that formula sits in one cell. And if you wanted to count the number of Rome's
that appear in B2:B99 after you filter on some other column (mixture of Rome,
Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace
Vrange with B2:B99 in that formula.

Irv wrote:

I understand what you suggested, however, it doesn't address the issue of the
result changing when applying filters to other columns in the spreadsheet.

I was able to get the total number of cells that contains, for instance
"DP*", however when I applied filters to another column the total number of
cells containing DP did not change.

"Dave Peterson" wrote:

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

"BASFMark wrote"

Irv, you should not have added the last 20 characters to the formula
( --(F2:F11119="DP*")) )
That last line from Dave was just to let you know what Vrange represented in
the example.
I used the formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET(V2:V11119,ROW(V2:V11 119)-MIN(ROW(V2:V11119)),,1)))
and it worked just fine, counting only the records that were left after
filtering (but not looking for any specific text).


"Irv" wrote:

I tried Aladin's suggested formula, but it did not return any result:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2:F11119,ROW(F2:F11 119)-MIN(ROW(F2:F11119)),,1)),--(F2:F11119="DP*"))



"Dave Peterson" wrote:

So you want to do the equivalent of =countif() but with filtered data?

If that's close to what you mean:

Aladin Akyurek posted this:

If you're trying to count the occurrences of a certain text in V which
is part of an AutoFiltered range....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),
--(Vrange="Rome"))

would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.

===
that formula sits in one cell. And if you wanted to count the number of Rome's
that appear in B2:B99 after you filter on some other column (mixture of Rome,
Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace
Vrange with B2:B99 in that formula.

Irv wrote:

I understand what you suggested, however, it doesn't address the issue of the
result changing when applying filters to other columns in the spreadsheet.

I was able to get the total number of cells that contains, for instance
"DP*", however when I applied filters to another column the total number of
cells containing DP did not change.

"Dave Peterson" wrote:

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a column that has
data (text, &/or number), but the kicker is I need that number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

You realize that you post a message to a thread from October 2006?

--
Regards,

Peo Sjoblom


"BASFMark" wrote in message
...
"BASFMark wrote"

Irv, you should not have added the last 20 characters to the formula
( --(F2:F11119="DP*")) )
That last line from Dave was just to let you know what Vrange represented
in
the example.
I used the formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET(V2:V11119,ROW(V2:V11 119)-MIN(ROW(V2:V11119)),,1)))
and it worked just fine, counting only the records that were left after
filtering (but not looking for any specific text).


"Irv" wrote:

I tried Aladin's suggested formula, but it did not return any result:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2:F11119,ROW(F2:F11 119)-MIN(ROW(F2:F11119)),,1)),--(F2:F11119="DP*"))



"Dave Peterson" wrote:

So you want to do the equivalent of =countif() but with filtered data?

If that's close to what you mean:

Aladin Akyurek posted this:

If you're trying to count the occurrences of a certain text in V which
is part of an AutoFiltered range....

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),
--(Vrange="Rome"))

would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.

===
that formula sits in one cell. And if you wanted to count the number
of Rome's
that appear in B2:B99 after you filter on some other column (mixture of
Rome,
Paris, London still appear in B2:B99, you'd used Aladin's formula--just
replace
Vrange with B2:B99 in that formula.

Irv wrote:

I understand what you suggested, however, it doesn't address the
issue of the
result changing when applying filters to other columns in the
spreadsheet.

I was able to get the total number of cells that contains, for
instance
"DP*", however when I applied filters to another column the total
number of
cells containing DP did not change.

"Dave Peterson" wrote:

=subtotal(9, f2:f11119)
Sums the values in those visible cells

If you look at Excel's help, you'll see that you have an option to
Count Numbers
or Count (non-empty) in that range, too.



Irv wrote:

I tried that but it didn't work. =subtotal(9,f2:f11119) is what I
used.

"Dave Peterson" wrote:

Take a look at =subtotal() in Excel's help.

Irv wrote:

I need to find a formula that counts the number of cells in a
column that has
data (text, &/or number), but the kicker is I need that
number to change when
I use filters.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Using the "Count" function with filters?
 

I have a Data in a column all Values ranging from -4 to 25. Now this values
in the Column are in Filter category. Now can you please suggest me How can I
Count the no of Values equal to or less than 0 , i.e. "=<0" after applying
filter to this Column.

Regards,
icsonu

Using the "Count" function with filters?
 

Assuming your data is in A2:A100 then this formula will count numbers
<=0 in that range which aren't filtered out

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),--
(A2:A100<=0))

Using the function of filters?
 

Can I get a specific Word or Name in a displayed Column which is Filtered.
Eg. Say i want to find a Name James in a filter column of heading Names of
Students.