In this article, we will learn how to use Subtotal and Countifs together in formula in Excel. This is a useful technique to count the number of cells that meet certain criteria in a filtered range of data. For example, we can use this method to count the number of sales of a specific product in a certain region, even after applying a filter to the data.
The Subtotal function in Excel returns a subtotal of a range of data, depending on the function number and the option to include or exclude hidden rows. The function number can be any of the following:
- 1: AVERAGE
- 2: COUNT
- 3: COUNTA
- 4: MAX
- 5: MIN
- 6: PRODUCT
- 7: STDEV
- 8: STDEVP
- 9: SUM
- 10: VAR
- 11: VARP
The option to include or exclude hidden rows can be either 0, 1, or 2:
- 0: Include hidden rows
- 1: Exclude hidden rows
- 2: Only include hidden rows
The syntax of the Subtotal function is:
=SUBTOTAL(function_num,ref1,[ref2],...)
Where function_num is the function number, ref1 is the first range of data, and ref2, … are optional additional ranges of data.
The Countifs function in Excel counts the number of cells that meet one or more criteria in one or more ranges. The criteria can be numbers, text, logical values, or expressions. The syntax of the Countifs function is:
=COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...)
Where criteria_range1 is the first range of cells to evaluate, criteria1 is the first criterion to apply, and criteria_range2, criteria2, … are optional additional ranges and criteria to apply.
Procedures
To use Subtotal and Countifs together in formula in Excel, we need to use the following formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(criteria_range,ROW(criteria_range)-ROW(first_cell),0,1)),--(criteria_range=criteria))
Where criteria_range is the range of cells that contains the criteria values, first_cell is the first cell of the criteria range, and criteria is the criterion to apply.
This formula works by using the Offset function to return a reference to each cell in the criteria range, and then using the Subtotal function with function number 3 to return the count of visible cells in each reference. The Subtotal function ignores the hidden rows that are filtered out by the filter. The Sumproduct function then multiplies the count of visible cells by the result of the comparison between the criteria range and the criterion, and sums up the products. The double negative sign (–) converts the TRUE and FALSE values to 1 and 0, respectively.
Example
Let’s see an example of how to use Subtotal and Countifs together in formula in Excel. Suppose we have the following data table that shows the sales of different products in different regions:
| Region | Product | Sales |
|---|---|---|
| North | A | 100 |
| North | B | 200 |
| North | C | 300 |
| South | A | 400 |
| South | B | 500 |
| South | C | 600 |
| East | A | 700 |
| East | B | 800 |
| East | C | 900 |
| West | A | 1000 |
| West | B | 1100 |
| West | C | 1200 |
We want to count the number of sales of product A in the North region, even after applying a filter to the data. To do this, we can use the following formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B13,ROW(B2:B13)-ROW(B2),0,1)),--(B2:B13="A"))
Where B2:B13 is the criteria range, B2 is the first cell of the criteria range, and “A” is the criterion.
The result of this formula is 1, as shown in the following table:
| Region | Product | Sales | Count |
|---|---|---|---|
| North | A | 100 | 1 |
| North | B | 200 | |
| North | C | 300 | |
| South | A | 400 | |
| South | B | 500 | |
| South | C | 600 | |
| East | A | 700 | |
| East | B | 800 | |
| East | C | 900 | |
| West | A | 1000 | |
| West | B | 1100 | |
| West | C | 1200 |
If we apply a filter to the data and only show the rows where the region is North, the formula still returns 1, as shown in the following table:
| Region | Product | Sales | Count |
|---|---|---|---|
| North | A | 100 | 1 |
| North | B | 200 | |
| North | C | 300 |
Other approaches
There are other ways to use Subtotal and Countifs together in formula in Excel, such as using the Aggregate function or the Sumifs function. However, these methods may have some limitations or drawbacks compared to the method described above. For example, the Aggregate function is only available in Excel 2010 and later versions, and the Sumifs function may not work properly if the criteria range contains blank cells or errors. Therefore, the method using the Sumproduct, Subtotal, and Offset functions is the most reliable and versatile way to use Subtotal and Countifs together in formula in Excel.
