So, I am trying to find a way to get the completion percentage of different activities. I already found a way to get it the consolidated value (for all groups without filtering), however now I would like to obtain the the completion when filtering the group. So, this is how my table looks like:
| ID | Group | Activity 1 | Activity 2 |
|---|---|---|---|
| 1234 | ADMI | 11/21/2021 | 03/08/2021 |
| 2234 | ENGINEER | blank cell (but with formula inside) | 03/05/2021 |
| 2134 | HR | 09/21/2021 | 03/10/2021 |
| 3234 | OPERATOR | blank cell (but with formula inside) | 05/10/2021 |
| 3334 | OPERATOR | blank cell (but with formula inside) | 01/03/2021 |
| 3434 | HR | 11/21/2021 | 03/08/2021 |
If the cell has a date value, then it means that the person did the activity. If the cell is blank, then it means that the person didn´t perform the activity.
As I mentioned previously I already found a way using: "COUNTA" - "COUNTBLANK" and dividing by the number of employees to get the percentage for each activity without filtering and you can find above the output:
| Activity 1 Completion Percentage | Activity 2 Completion Percentage |
|---|---|
| 50% | 100% |
But now I would like to know how much it would be in case I filter the group. So, let´s suppose that I filter HR, this is going to look like be my desired output:
| Activity 1 Completion Percentage | Activity 2 Completion Percentage |
|---|---|
| 100% | 100% |
I know that I need to use subtotal but it does not appear the option to count blanks. Does anyone know how can I solve this issue without having to use a pivot table?