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
|