| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using the "count" function.. | Excel Discussion (Misc queries) | |||
| VBA | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
