SUBTOTAL respects filters but takes no criteria; COUNTIF takes criteria but ignores filters. To get both, combine them with SUMPRODUCT and OFFSET: =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0)))) counts the rows matching E1 among only the visible rows.
The OFFSET part builds a one-cell reference for every row in the range, so SUBTOTAL evaluates each row separately and returns 1 for visible and 0 for hidden; multiplying that by your TRUE/FALSE condition leaves 1 only where both hold, and SUMPRODUCT adds them up. To sum rather than count, multiply in the value column: =SUMPRODUCT((C2:C41=E1)*SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))*B2:B41).
One practical note: OFFSET is volatile, so it recalculates on every change and slows down noticeably over a few thousand rows. On large data, a helper column with =SUBTOTAL(3,C2) filled down, then a plain =COUNTIFS(C2:C41,E1,D2:D41,1) against it, does the same job far faster.
THIS FORMULA IS PURE MAGIC.
If you want to write a SUBTOTAL formula in Excel with IF (condition), you need to use multiple functions to do this. But before we do this, let’s understand the data we have for this example.
In the above example, you have three columns:
- Name
- Age Slab
- Gender
And when you filter a slab from the Age Slab column, it shows the count of females in cell F1. So that means we have a formula that shows the count of filtered values but with a condition. The formula we have:
=SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))
Understanding SUBTOTAL IF Formula
This formula uses five functions: SUMPRODUCT, SUBTOTAL, OFFSET, ROW, and MIN. Therefore, to understand this formula, we need to split it into multiple parts.
1. MIN(ROW(C2:C41))
This part of the formula uses MIN and ROW functions.
- In ROW, we have referred to the “Gender” column, and it returns an array of row numbers.
- After that, MIN takes that array of row numbers and returns the minimum row number. So that’s why we have got 2 in this part of the formula.
2. ROW(C2:C41)
In this part, we only have the ROW function, which returns an array of row numbers.
3. OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0)
Now we have the OFFSET function. It helps you to create a reference to a range using a cell reference as a starting point. In the reference argument, we have referred to cell C2, the first cell from where our gender range starts.
In the rows argument, we have that part of the formula discussed above in the first two parts. After that, in the cols argument, we used 0. With all these, OFFSET returns an array of single-cell references, one for each cell of the “Gender” column. That’s the trick: SUBTOTAL can then check each cell on its own.
4. SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))
We have used the array returned by the OFFSET in the SUBTOTAL. And in the function_num, we have used 3, which tells SUBTOTAL to use the COUNTA function for calculation.
When you use a filter on the column “Age Slab”, this SUBTOTAL part of the formula returns an array of 0 and 1.
In this array, we have 1 for the rows that are visible after the filter (the rows that match the value you have filtered for) and 0 for the rows the filter hides. See the example below:
5. (C2:C41=E1)
This part of the formula returns an array by testing a condition. In this condition, we are testing if the value in the range is “Female”, and it returns TRUE and FALSE in the array.
In this array, we have TRUE for the value “Female” and FALSE for others.
6. Last Part
In the end, we have two arrays in the SUMPRODUCT. And we also have an asterisk operator in between those arrays.
When we multiply both arrays with each other, we have a single array with 0 and 1. In this array, one (1) is for the value “Female” in the gender and 21-30 for the “Age Slab”.
In the end, SUMPRODUCT returns the sum by using this array. And this sum is the count of cells with the value “Female” in the gender column when you filter the 21-30 slab in the “Age Slab” column.
SUBTOTAL IF to Sum Visible Values
You can use the same idea to sum instead of count. Use 9 (SUM) in SUBTOTAL and point OFFSET to the column with the numbers. The following formula sums the amounts in column D for the visible rows where the gender is the one in E1:
=SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(9,OFFSET(D2,ROW(D2:D41)-MIN(ROW(D2:D41)),0))))
Ignore Manually Hidden Rows as Well
SUBTOTAL with 3 or 9 ignores the rows hidden by a filter, but it still counts the rows that you have hidden manually (right-click ➜ Hide). If you also want to skip those, use 103 instead of 3 (or 109 instead of 9):
=SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(103,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))
Simpler Method with a Helper Column
If the long formula feels too complex, add a helper column next to your data with the following formula in D2 (and fill it down). It returns 1 for a visible row and 0 for a hidden one:
=SUBTOTAL(103,C2)
After that, count the visible rows with your condition with COUNTIFS, where D2:D41 is the helper column:
=COUNTIFS(C2:C41,E1,D2:D41,1)
Frequently Asked Questions
The rows are hidden manually, not by a filter. SUBTOTAL with 3 ignores only filtered-out rows. Use 103 to ignore both.
Can I use more than one condition?
Yes. Add another condition array inside SUMPRODUCT, like (B2:B41=”21-30″)*(C2:C41=E1)*SUBTOTAL(…).
Does this formula work in Google Sheets?
Yes. SUMPRODUCT, SUBTOTAL, OFFSET, ROW and MIN all work the same way in Google Sheets.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.