How to Use COUNTA from SUBTOTAL Function in Excel

In this tutorial, I am going to show you 5 suitable examples of how to use the SUBTOTAL COUNTA function in Excel. You can use these examples in your own dataset to find out various subtotal values. Throughout this tutorial, you will also learn some important Excel tools and techniques which will be very useful in any Excel-related task.

COUNTA Function as SUBTOTAL Parameter

The SUBTOTAL function in Excel can take multiple functions as its first parameter. We can specify the COUNTA function by passing 3 as the first parameter. After that, we need to pass the range of values that we want to get the subtotal from. The SUBTOTAL function can either include or exclude certain values which make sit very useful for calculation in a table.

How to Use SUBTOTAL COUNTA Function in  Excel: 5 Suitable Examples

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 2 unique columns which are Items and Sales (units). Although we may vary the number of columns later on if that is needed.

1. Counting Item Types

The most simple example of using the COUNTA function inside the SUBTOTAL function in Excel is to count all the cells that have any type of data. Let us see this in practice.

Steps:

• First, go to cell C10 and insert the following formula:

• Now, press Enter and this will calculate the total different item types inside cell C10.

Read More: How to Use COUNTA Function with Criteria in Excel

2. Using SUBTOTAL in Filtered Items

When we use the COUNTA function inside the SUBTOTAL function in Excel, it will exclude all the data we filter out using the Excel Filter feature. Follow the steps below to build this example.

Steps:

• To begin with, double-click on cell C10 and enter the below formula:

• Next, press the Enter key which should give you the item types count.
• Then, go to the Data tab and click on Filter.

• Now, click on the drop-down on the Items heading and uncheck Raspberries for example.
• Then click OK.

• As a result, the formula will no longer count the row we just filtered and the total count will now be 5.

3. Finding Nested SUBTOTAL

If we include any nested SUBTOTAL formula along with the data rows in Excel, the COUNTA function will not take them into count. Let us see this example below in detail.

Steps:

• To begin this method, double-click on cell D8 and insert the formula below:

• Next, press the Enter key and type in the following formula inside cell D12:

• After that, press the Enter key again and enter this formula in the cell D13:

• Finally, press Enter and you should see that the last formula did not count the previous SUBTOTAL values and counted only the items.

4. Counting Data with Hidden Row

In case we hide any row manually when using the COUNTA function inside the SUBTOTAL function, the data will come into count even though we can not see that row. Follow the steps below to do this.

Steps:

• As previously, insert the below formula inside cell C10:

• Then, press Enter and right-click on any of the row numbers.
• Now, click on Hide.

• Consequently, this will hide the row but the formula will still count that hidden row making the resulting value still 6.

5. Applying SUBTOTAL in VBA

If you are familiar with VBA in Excel, you can apply the COUNTA function inside the SUBTOTAL function with just a few lines of code. Let us see how we can do this.

Steps:

• For this method, go to the Developer tab and select Visual Basic.

• Now, select Insert in the VBA window and click on Module.

• Next, type in the formula below in the new window:

Public Sub Subtotal_Counta()
Range("C11").Formula = "=SUBTOTAL(3,C5:C10)"
End Sub

• Then, open the macro from the Developer tab by clicking on Macros.

• Now, in the Macro window, select the Subtotal_Counta macro and click Run.

• As a result, the VBA code will calculate the total item types as 6.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to use the SUBTOTAL COUNTA function in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.

Related Articles

• Dynamic Ranges with OFFSET and COUNTA Functions in Excel
• Difference Between COUNT and COUNTA Functions in Excel
• [Fixed] Excel COUNTA Function Not Working

<< Go Back to Excel COUNTA Function | Excel Functions | Learn Excel