How to Use SUBTOTAL with COUNTIF in Excel

Using SUBTOTAL with COUNTIF in Excel allows you to count the number of cells that meet certain criteria, while also being able to filter the data. SUBTOTAL function can perform various calculations like COUNT, SUM, AVERAGE, etc., depending on the function number provided. However, SUBTOTAL cannot be combined directly with COUNTIF, so we need to use a helper column to achieve this.

Here's a step-by-step guide on how to use SUBTOTAL with COUNTIF in Excel:

1. Open your Excel spreadsheet and make sure you have the data you want to work with.
2. Add a helper column next to your data. This column will be used to store the results of the COUNTIF function. For example, if your data is in columns A and B, insert a new column in C.
3. In the first cell of the helper column (C1, for example), enter the COUNTIF function based on your criteria. For example, if you want to count the number of cells in column A that are greater than 10, you would enter the formula =COUNTIF(A:A,">10").
4. Copy the formula down the helper column for all the rows in your data.
5. In a new cell, enter the SUBTOTAL function to sum the values in the helper column. For example, if you want to sum the COUNTIF results in column C, you would enter the formula =SUBTOTAL(9,C:C). The number 9 in the formula represents the function number for SUM in SUBTOTAL.
6. The result in the cell where you entered the SUBTOTAL function will now display the count of cells that meet your criteria, and it will update automatically when you apply filters to your data.

Example

Let's say we have a list of sales data, and we want to count the number of sales greater than $500, while being able to filter the data by salesperson.

1. Here's the data in Excel:

2. Add a helper column in column C and enter the COUNTIF formula in C2: =IF(B2>500,1,0).

3. Copy the formula down the helper column.
4. In a new cell, enter the SUBTOTAL function to sum the values in the helper column: =SUBTOTAL(9,C:C). The result will be 3, as there are three sales greater than $500.
5. Now you can apply filters to your data, and the SUBTOTAL function will update the count automatically based on the visible rows. For example, if you filter the data to only show sales by John, the SUBTOTAL function will display 2, as there are two sales by John greater than $500.