How to count blank cells using subtotal

Occasional Contributor

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

2234ENGINEERblank cell (but with formula inside)03/05/2021
3234OPERATORblank cell (but with formula inside)05/10/2021
3334OPERATORblank cell (but with formula inside)01/03/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?

4 Replies
best response confirmed by brunotavares (Occasional Contributor)

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

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