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?

 

[Sergius]

Hello! Provide a small snippet of your table to make it easier to understand what you want.

 

[Seyk1701]

Hello! Provide a small snippet of your table to make it easier to understand what you want.

 

[Seyk1701]

Hello! Provide a small snippet of your table to make it easier to understand what you want.

Thank you for your response. I have drop down menu for choosing manager name and level of manager. So the rows in my table are not fixed. Hence why I need the formula to ignore blanks or for it to be dynamic.

 

[Joe4]

Your formula should already ignore blanks, as COUNTIF only counts cells meeting your criteria, and COUNTA only counts cells that have something in them.
So as long cells you are checking REALLY are blank (and don't contain things like formulas or single spaces), your formula should already ignore all the blanks.

 

[Seyk1701]

Your formula should already ignore blanks, as COUNTIF only counts cells meeting your criteria, and COUNTA only counts cells that have something in them.
So as long cells you are checking REALLY are blank (and don't contain things like formulas or single spaces), your formula should already ignore all the blanks.

Ahh thought as much. So my issue is with the column where I have "Exceeded Expected Duration" and "On Target or Below". I stretched the formula down to 15000 rows. So I need this formula to be a dynamic range according to the manager selected. The formula is ... =IF(I9="","",IF(I9>R9,"Expected Duration Exceeded","On Target or Below"))

 

[Asbestos_Jen]

I think if you modify your formula to account for your manager name, it should work :

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

 

[Seyk1701]

I think if you modify your formula to account for your manager name, it should work :

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

I think if you modify your formula to account for your manager name, it should work :

=COUNTIFS(L9:L10000,"On Target or Below",A9:A10000,D3)/COUNTIF(A9:A10000,D3)[/CODE
[/QUOTE]

That's done the trick! Thank you.

 

[Seyk1701]

I think if you modify your formula to account for your manager name, it should work :

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

I think if you modify your formula to account for your manager name, it should work :

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

Hey, I think my issue is with the preceding formula to check if expected duration has been exceeded or not, from which I work out the percentage.

This formula:

 

[Asbestos_Jen]

If the issue is that it's counting all cells belonging to that manager, not just the ones that say either "expected duration exceeded" or "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,"<>"&"")