Percentage formula to ignore blanks

[Seyk1701]

I have a manager summary page with a drop down menu, to select the managers name. This then lists all the employees that report to the selected manager, data such as 'time taken on a job' vs 'expected time'. I then have a calculated column, with "Exceeded Expected Duration" and "On Target or below".

When a manager is selected, he may have 10 employees working for him, or 1000, I want to see what this managers "On Target or Below" percentage is. My problem is, for the formula to cover all employees under a certain manager, I need to choose a large column range. My formula is: =COUNTIF(L9:L10000,"On Target or Below")/COUNTA(L9:L10000) ... is there any way to make this dynamic? In that the 10000, always changes according to the amount of employees? Or how can I ignore blanks?

 

[Seyk1701]

If the issue is that it's counting all cells belonging to that manager, not just the ones that say either "expected duration exceeded" and "on target or below", you can modify it like this:

=COUNTIFS(L9:L10000,"On Target or Below",A9:A10000,D3)/COUNTIFS(A9:A10000,D3,L9:L10000,"<>"&"")

I thought I edited my response. Yep the solution work, thank you very much!

 

[Asbestos_Jen]

Glad to have helped.