SubTotal Count(2)

Hi,
I a formula in one of my cells so that when the data is filtered it will add up all the cells that have a yes in them. However I'm getting an error. If I use the countif formula it still adds all the cells with yes that are not in the filter selection.

So can someone help me with the correct formula to use when I filter my data?

Here is the problem

Please Login or Register  to view this content.

This works fine but wont work with the filter on it still counts all the "Yes".

Please Login or Register  to view this content.

Thank You, Mike

TRY:

Please Login or Register  to view this content.

You can't pass a parameter "Yes" to the SUBTOTAL(2, formula. But if you filter by that column showing only YES answers, your Subtotal formula will instantly tell you how many are cells are visible.

So, don't think of the formula as count='yes' and just think of it as count='visible'

Now, you can do it this way:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(G6:G6000,ROW(G6:G6000)
-MIN(ROW(G6:G6000)),,1)), --(G6:G6000="Yes"))

(Source)

Originally Posted by realniceguy5000

Outstanding. Guys It's Working now.

It would have taking some time if ever for me to get that formula working on my own. I tried both options.

I didn't have any luck with nbvc I kept getting 0 as the anwser,however you got me thinking in the right direction. Thank You

JB your formula worked... Thank You

Thanks Again for the help, Mike

That is because you really wanted the COUNTA() function (i.e. 3) instead of the COUNT() function (i.e. 2) as you had originally implied.