How to Use SUBTOTAL with COUNTIF in Excel

In this tutorial, you will learn how to use SUBTOTAL with COUNTIF in Excel.

Subtotals are calculated with aggregation functions such as Sum and Average using the SUBTOTAL function. Each column can display multiple types of aggregation functions. Grand totals are derived from detail data, not from subtotal values.

Count the number of cells that meet a condition using COUNTIF, one of the statistical functions.

Once ready, we’ll get started by utilizing real-world examples to show you how to use SUBTOTAL with COUNTIF in Excel.

Use SUBTOTAL with COUNTIF in Excel

Before we begin we will need a group of data to use SUBTOTAL with COUNTIF in Excel.

Step 1

First, you need to have a clean and tidy group of data.

Step 2

We will need to filter the initial dataset. To do this, select the entire range and go to the ‘Data’ tab. Then select ‘Filter’ in the ‘Sorting & Filtering’ section.

Step 3

Then click the dropdown arrow next to the Class column. Then, check the box for B, because we only count cells in the B class. Finally, press ‘Apply’.

Step 4

Now that we’ve filtered the dataset, we can apply a formula. To do this, use the formula =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),–(B2:B11=”Girl”)). Then press Enter to return the results.

Step 5

Once we are done, the formula will return ‘0’.