Functions and AutoFilter

[Beth]

I would like my function result to reflect the change in
the worksheet when filtering using AutoFilter.

For example, I have a COUNTIF function that returns the
same result regardless of the change in the data viewed
when I filter the sheet. I would like for the result to
change as the sheet is filtered.

Any help would be appreciated.

 

[Peo Sjoblom]

How does you countif look like? Here's one way that will work as a countif
and not count hidden cells

=SUMPRODUCT(($B$2:$B$20>5)*(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$20)-MIN(ROW($
B$2:$B$20)),,))))

will count filtered cells in B2:B20 that are greater than 5

 

[Beth]

=COUNTIF(J:J,"Not Posted") This simply gives a count of
the cells in column J that have the value "Not Posted".
That's fine, but I would like the results to change if a
filter (using AutoFilter) has been applied. By default,
it does not.

 

[Peo Sjoblom]

So why didn't you try to adapt the formula to your data?

=SUMPRODUCT(($J$2:$J$200="Not
Posted")*(SUBTOTAL(3,OFFSET($J$2,ROW($J$2:$J$200)-MIN(ROW($J$2:$J$200)),,)))
)

assuming you have a header in J1, adapt to fit the dimension of your column

The above formula will count filtered "Not Posted" but not hidden like the
countif formula..