SOLVED

# How to count blank cells using subtotal

Occasional Contributor

# 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:

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)
Solution

# Re: How to count blank cells using subtotal

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

# Re: How to count blank cells using subtotal

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

# Re: How to count blank cells using subtotal

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

# Re: How to count blank cells using subtotal

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