Filter countif problem

Hi grdnryn. Welcome to the forum.

With target % in E1 try

Formula:

Please Login or Register  to view this content.

Then filter and see what happens.

I'll do my best.

This explanation makes heavy use of the F9 function key (upper keyboard) and the Evaluate Formula feature found on the ribbon (Formulas > Evaluate formula). You can follow along using F9 in the steps below.

I have made a much smaller version of your upload for this. The reasons will become apparent.

In D1 of the attached find this formula. It's the same one as before only referencing a much smaller range.

Formula:

Please Login or Register  to view this content.

With D1 the active cell select this portion of the formula in the formula bar and hit F9.

OFFSET(B3,ROW(B3:B102)-MIN(ROW(B3:B102)),,)

You will see an "array" of each number that is in B3:B102. What is important to understand here is that OFFSET has the ability to return ranges as separate discreet cells even though the returns appear to be an array in memory. That's important because this set of discreet cells is being passed to SUBTOTAL, and SUBTOTAL will only accept ranges and discreet cell references ... never arrays.

Extend the selection in the formula bar to include the following and hit F9 again.

SUBTOTAL(2,OFFSET(B3,ROW(B3:B102)-MIN(ROW(B3:B102)),,))

You will see an array of all ones. This is because SUBTOTAL (function_num 2) returns a count of each number in each of those OFFSET returns.

This is not so exciting so far. What is important to know in this step is that SUBTOTAL has the ability to ignore invisible and hidden rows. For count the function numbers are 2 and 102. Check the help file on those.

In this case it's a no-brainer. The counts are all 1s which is what we want for the next steps.

Now select just this portion of the formula and hit F9.

(B3:B102>E1)

You will see an array of TRUE/FALSE. When math operations are performed on these it coerces TRUE/FALSE into their underlying numeric values 1/0.

Extend the selection to include this (and hit F9).

((B3:B102>E1)*SUBTOTAL(2,OFFSET(B3,ROW(B3:B102)-MIN(ROW(B3:B102)),,)))

You will see an array of resulting 1s and 0s. SUMPRODUCT then sums those up.

The filtering "magic" is in the SUBTOTAL function.

Now using F9 examines each step out of context. You have to intuit the order in which to do it.

Try applying Evaluate Formula (aka: Fx) to see all of this in context.

With D1 active and the Evaluate formula dialogue window visible click "Evaluate" repeatedly. Each click reveals step-by-step how Excel solves the formula. Each step is in context.

You will recall I said that the reasons for a smaller file would become apparent. Well here it is. The viewing port is painfully small. Try using it on the original upload.

Now repeat all the F9 steps above with the data filtered. Can you tell me what is happening differently?

Does this help?

You are welcome. Glad it helps. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.