How to Use SUBTOTAL with COUNTIF in Excel

by Published on

You can use the following formula to combine the SUBTOTAL and COUNTIF functions in Excel:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Guard"))

This particular formula allows you to count the number of cells in the range B2:B11 equal to “Guard” even after that range of cells has been filtered in some way.

The following example shows how to use this formula in practice.

Example: How to Use SUBTOTAL with COUNTIF in Excel

Suppose we have the following dataset that contains information about various basketball players:

Next, let’s filter the data to only show the rows where the players are in the West conference.

To do so, highlight the cell range A1:C11. Then click the Data tab along the top ribbon and click the Filter button.

Then click the dropdown arrow next to Conference and make sure that only the box next to West is checked, then click OK:

The data will automatically be filtered to only show the rows where the Conference column is equal to West:

If we attempt to use the COUNTIF() function to count the number of rows where Position is equal to “Guard”, it will actually return the count of the rows equal to “Guard” in the original dataset:

Instead, we need to use the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Guard"))

The following screenshot shows how to use this formula in practice:

This function only counts the number of visible rows where Position is equal to “Guard”, which happens to be 4 rows.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Delete Filtered Rows in Excel
How to Count Filtered Rows in Excel
How to Sum Filtered Rows in Excel

3 Replies to “How to Use SUBTOTAL with COUNTIF in Excel”

  2. I tried to apply this to something I am working on yet the result is 0. Good example would be the sample above, but with 2 columns filtered, you want to see how many instances a score was repeated, or perhaps below a set number. Example would be that with the columns filtered for West and Guard, how many were less than 20? I have a file with about 13000 rows of data over about 8 columns. I filter 4 columns, and want to know how many values in column 5 are below a value. I get zero, and it should be much much more.

    Reply

    1. Hi John…In Excel, the `SUBTOTAL` function is tricky when combining it with conditional counting, like `COUNTIF`, because `SUBTOTAL` doesn’t directly support conditional criteria. However, you can use an alternative method that counts filtered rows below a threshold in column 5 by using an array formula with `SUBTOTAL` and `IF`.

      To count values below a set threshold in column 5 with multiple filters applied, try this formula:

      “`excel
      =SUMPRODUCT(SUBTOTAL(3, OFFSET(E2:E13001, ROW(E2:E13001)-MIN(ROW(E2:E13001)), 0, 1)), –(E2:E13001 < 20)) ``` ### Explanation: 1. `E2:E13001` represents the range in column 5 where you're checking if the values are below a threshold (replace `20` with your desired value). 2. `SUBTOTAL(3, OFFSET(...))` dynamically counts only the visible rows after filtering. 3. The `--` before `(E2:E13001 < 20)` converts the Boolean result to a numeric form that `SUMPRODUCT` can work with. If this approach still shows zero, double-check that: - Column 5’s values are formatted as numbers. - The filters in other columns are set correctly. Let me know if you need further customization for your specific setup!

      Reply

Leave a Reply