This tutorial will demonstrate how to calculate “subtotal if”, counting only visible rows with criteria.

SUBTOTAL IF Formula

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.)

=SUBTOTAL(3,$D$2:$D$14)

SUBTOTAL COUNT Filtered

SUBTOTAL COUNT Excel

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:

SUBTOTAL Value Range

Supposed we are asked to count the number of members that have attended an event dynamically as we manually filter the list like so:

SUBTOTAL IF Formula

To accomplish this, we can use this formula:

=SUMPRODUCT((<values range>=<criteria>)*(SUBTOTAL(3,OFFSET(<first cell in range>,ROW(<values range>)-MIN(ROW(<values range>)),0))))
=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:

=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:

=(<values range>=<criteria>)
=(D2:D14="Attended")

Output:

{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.

SUBTOTAL Filtered Value Range

SUBTOTAL Value Range

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:

=SUBTOTAL(3,OFFSET(<first cell in range>,ROW(<values range>)-MIN(ROW(<values range>)),0))
=SUBTOTAL(3,OFFSET(D2,ROW(D2:D14)-MIN(ROW(D2:D14)),0))

Output:

{1;1;0;0;1;1}

Stitching the two together:

=SUMPRODUCT({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4

SUBTOTAL IF with Multiple Criteria

To add multiple criteria, simply multiple more criteria together within the SUMPRODUCT like so:

SUBTOTAL IFS with multiple criteria

=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))))
=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:

SubTotal IF