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.

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.

Steps:

• Go to cell C10 and insert the following formula:

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

Method 2 – Using SUBTOTAL in Filtered Items

Steps:

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

• 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

Steps:

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

• 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

Steps:

• Insert the below formula inside cell C10:

• 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

Steps:

• Go to the Developer tab and select Visual Basic.

• 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

• 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