How to count blank cells using subtotal

[brunotavares]

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:

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?

[#2 · Accepted solution]

@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.

[#3]

@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!

[#4]

@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!

[#5]

Don´t worry, Riny! You already helped me a lot, thanks a lot one more time!