SUBTOTAL IF Formula – Excel & Google Sheets
Download the example workbook
This tutorial will demonstrate how to calculate “subtotal if”, counting only visible rows with criteria.
SUBTOTAL Function
The SUBTOTAL function can perform various calculations on a range of data (count, sum, average, etc.). Most importantly, it can be used to calculate on only visible (filtered) rows. In this example, we will be using the function to count (COUNTA) visible rows by setting the SUBTOTAL function_num argument to 3 (A full list of possible functions can be found here.)
|
1 |
=SUBTOTAL(3,$D$2:$D$14) |
Notice how the results change as we manually filter rows.
SUBTOTAL IF
To create a “Subtotal If”, we will use a combination of SUMPRODUCT, SUBTOTAL, OFFSET, ROW, and MIN in an array formula. Using this combination, we can essentially create a generic “SUBTOTAL IF” function. Let’s walk through an example.
We have a list of members and their attendance status for each event:
Supposed we are asked to count the number of members that have attended an event dynamically as we manually filter the list like so:
To accomplish this, we can use this formula:
|
1 |
=SUMPRODUCT((<values range>=<criteria>)*(SUBTOTAL(3,OFFSET(<first cell in range>,ROW(<values range>)-MIN(ROW(<values range>)),0)))) |
|
1 |
=SUMPRODUCT((D2:D14="Attended")*(SUBTOTAL(3,OFFSET(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0)))) |
When using Excel 2019 and earlier, you must enter the array formula by pressing CTRL + SHIFT + ENTER to tell Excel that you’re entering an array formula. You’ll know the formula was entered properly as an array formula when curly brackets appear around the formula (see image above).
How does the formula work?
The formula works by multiplying two arrays inside of SUMPRODUCT, where the first array deals with our criteria and the second array filters to visible rows only:
|
1 |
=SUMPRODUCT(<criteria array>*<visibility array>) |
The Criteria Array
The criteria array evaluates each row in our values range (“Attended” Status in this example) and generates an array like this:
|
1 |
=(<values range>=<criteria>) |
|
1 |
=(D2:D14="Attended") |
Output:
|
1 |
{TRUE; FALSE; FALSE; TRUE; FALSE; TURE; TURE; TURE; FALSE; FALSE; TRUE; FALSE; TRUE} |
Note that the output in the first array in our formula ignore whether the row is visible or not, which is where our second array comes in to help.
The Visibility Array
Using SUBTOTAL to exclude non-visible rows in our range, we can generate our visibility array. However, SUBTOTAL alone will return a single value, while SUMPRODUCT is expecting an array of values. To work around this, we use OFFSET to pass one row at a time. This technique requires feeding OFFSET an array that contains one number at time. The second array looks like this:
|
1 |
=SUBTOTAL(3,OFFSET(<first cell in range>,ROW(<values range>)-MIN(ROW(<values range>)),0)) |
|
1 |
=SUBTOTAL(3,OFFSET(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0)) |
Output:
|
1 |
{1;1;0;0;1;1} |
Stitching the two together:
|
1 |
=SUMPRODUCT({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1}) |
|
1 |
= 4 |
SUBTOTAL IF with Multiple Criteria
To add multiple criteria, simply multiple more criteria together within the SUMPRODUCT like so:
|
1 |
=SUMPRODUCT((<values range 1>=<criteria 1>)*(<values range 2>=<criteria 2>)*(SUBTOTAL(3,OFFSET(<first cell in range>,ROW(<values range>)-MIN(ROW(<values range>)),0)))) |
|
1 |
=SUMPRODUCT((E2:E14="Attended")*(B2:B14=2019)*(SUBTOTAL(3,OFFSET(E2,ROW(E2:E14)-MIN(ROW(E2:E14)),0)))) |
SUBTOTAL IF in Google Sheets
The SUBTOTAL IF Function works exactly the same in Google Sheets as in Excel:
