filters with formulas

[Guest]

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]

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.

 

[Guest]

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]

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.