Home |
Search |
Today's Posts |
#1
|
|||
|
|||
filters with formulas
Help!
I have a field called date entered and lots of formulas. My problem is that when I applied the filter non of my formulas change. I am trying to keep a count going for every month itself and YTD stuff. Is there a way to do this right? I am going to deal with 31 different spreadsheets where they are being compared to one another. Oh boy big project! fourskunks |
#2
|
|||
|
|||
If your formulas looked like: =sum(a2:A99)
then maybe using =subtotal(9,a2:a99) would work better. =subtotal() can ignore cells that are hidden by an autofilter. fourskunks wrote: Help! I have a field called date entered and lots of formulas. My problem is that when I applied the filter non of my formulas change. I am trying to keep a count going for every month itself and YTD stuff. Is there a way to do this right? I am going to deal with 31 different spreadsheets where they are being compared to one another. Oh boy big project! fourskunks -- Dave Peterson |
#3
|
|||
|
|||
Ok, I forgot one thing and I found it.
Column one is date entered. Two three four name fields Four is male or female Five is pick from the language types (i. e. english, spanish, french etc.) Six is 7 choices to pick from. I want to apply a filter on date entered for like all of October and total count of the male, female, different languages, and the 7 choices for that month only. Does this make any sense? I have 23 workbooks to deal with. Help? fourskunks "Dave Peterson" wrote: If your formulas looked like: =sum(a2:A99) then maybe using =subtotal(9,a2:a99) would work better. =subtotal() can ignore cells that are hidden by an autofilter. fourskunks wrote: Help! I have a field called date entered and lots of formulas. My problem is that when I applied the filter non of my formulas change. I am trying to keep a count going for every month itself and YTD stuff. Is there a way to do this right? I am going to deal with 31 different spreadsheets where they are being compared to one another. Oh boy big project! fourskunks -- Dave Peterson |
#4
|
|||
|
|||
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. fourskunks wrote: Ok, I forgot one thing and I found it. Column one is date entered. Two three four name fields Four is male or female Five is pick from the language types (i. e. english, spanish, french etc.) Six is 7 choices to pick from. I want to apply a filter on date entered for like all of October and total count of the male, female, different languages, and the 7 choices for that month only. Does this make any sense? I have 23 workbooks to deal with. Help? fourskunks "Dave Peterson" wrote: If your formulas looked like: =sum(a2:A99) then maybe using =subtotal(9,a2:a99) would work better. =subtotal() can ignore cells that are hidden by an autofilter. fourskunks wrote: Help! I have a field called date entered and lots of formulas. My problem is that when I applied the filter non of my formulas change. I am trying to keep a count going for every month itself and YTD stuff. Is there a way to do this right? I am going to deal with 31 different spreadsheets where they are being compared to one another. Oh boy big project! fourskunks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Help, Urgent Excel Formulas are not calculating | Excel Discussion (Misc queries) | |||
Problem with named formula's | Excel Worksheet Functions | |||
Formulas | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |