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

Keep all the cells in General format. For all the datasets, we have 2 unique columns which are Items and Sales (units). We may vary the number of columns later on if that is needed.

subtotal counta in excel

Method 1 – Counting Item Types

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.


  • Go to cell C10 and insert the following formula:

how to use the SUBTOTAL COUNTA function in excel for counting item types

  • Press Enter to calculate the total different item types inside cell C10.


Method 2 – Using SUBTOTAL in Filtered Items


  • Double-click on cell C10 and enter the formula below:

how to use the SUBTOTAL COUNTA function in excel to count filtered data

  • Press the Enter key, which should give you the item type count.
  • Go to the Data tab and click Filter.

  • Click on the drop-down on the Items heading and uncheck Raspberries for example.
  • Click OK.

  • The formula will no longer count the row we just filtered and the total count will now be 5.

Method 3 – Finding Nested SUBTOTAL


  • Double-click on cell D8 and insert the formula below:

how to use the SUBTOTAL COUNTA function in excel to find nested subtotal

  • Press the Enter key and type in the following formula inside cell D12:

  • Press the Enter key again and enter this formula in the cell D13:

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

Method 4 – Counting Data with Hidden Row


  • Insert the below formula inside cell C10:

how to use the SUBTOTAL COUNTA function in excel to count data with hidden rows

  • Press Enter and right-click on any of the row numbers.
  • Click Hide.

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

Method 5 – Applying SUBTOTAL in VBA


  • Go to the Developer tab and select Visual Basic.

how to use the SUBTOTAL COUNTA function in excel vba

  • Select Insert in the VBA window and click Module.

  • Type in the formula below in the new window:
Public Sub Subtotal_Counta()
Range("C11").Formula = "=SUBTOTAL(3,C5:C10)"
End Sub

  • Open the macro from the Developer tab by clicking on Macros.

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

  • The VBA code will calculate the total item types as 6.

Download Practice Workbook

You can download the practice workbook from here.

Related Articles

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







Get FREE Advanced Excel Exercises with Solutions!
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF