Forum Discussion
How to count blank cells using subtotal
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 | ADMIN | 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 |
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:
| 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:
| 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?
brunotavares If you transform the your data into a structured table and add a Total row, you can easily do what you need. But you can achieve the same with a regular table and SUBTOTAL, but then you need to enter the formula yourself. Both are demonstrated in the attached file.
4 Replies
brunotavares If you transform the your data into a structured table and add a Total row, you can easily do what you need. But you can achieve the same with a regular table and SUBTOTAL, but then you need to enter the formula yourself. Both are demonstrated in the attached file.
Riny_van_Eekelen Extremely helpful, Riny! I don´t know why but I am facing some issues when trying to transform my data into a structured table. Could you please tell me what are the steps to add this subtotal at the end of the table just like you did it? (I also attached the file where I need to add this subtotal)
Thanks a lot!
brunotavares Sorry but your table is linked to an external link. when I try to add a total row to the table everything turns #VALUE. Can't look at this in detail right now. Sorry!
