filtercountexcel-formulasubtotalwalkthrough

COUNTIF with table filtering, or how does the SUMPRODUCT/SUBTOTAL/OFFSET combination actually work?

Given the following Excel table:

+---+--------+----+----+
|   | A      | B  | C  |
+---+--------+----+----+
| 1 | Filter | V1 | V2 | <-- header row of the table, with filtering option
+---+--------+----+----+
| 2 | F1     | x  | x  |
| 3 | F2     | x  | y  |
| 4 | F1     | x  | y  |
+---+--------+----+----+

I need to count the number of x in V1 and V2 columns:

=COUNTIF(B2:C4, "x")

This works and returns 4. Now, if I filter the table so that the column Filter only contains F1 values:

+---+--------+----+----+
|   | A      | B  | C  |
+---+--------+----+----+
| 1 | Filter | V1 | V2 | <-- header row of the table, with filtering option
+---+--------+----+----+
| 2 | F1     | x  | x  |
| 4 | F1     | x  | y  |
+---+--------+----+----+.

The first formula still returns 4. I already found how to improve it so that is takes into account the possible filters (this solution can easily be found on the Internet):

=SUMPRODUCT((B2:C4="x") * SUBTOTAL(3, OFFSET(B2:C4, ROW(B2:C4) - MIN(ROW(B2:C4)), 0, 1, 1)))

This returns 3 for the second case, as expected. The question is: how does it work? Is anyone able to give me a detailed walkthrough of the second formula?

Solution

  • First lets have a look at SUMPRODUCT: SUMPRODUCT expect its arguments as arrays (matrices). So

    =SUMPRODUCT((B2:C4="x"))

    takes an array of {TRUE,TRUE;TRUE,FALSE;TRUE,FALSE} depending on if (B2:C4="x").

    =SUMPRODUCT((B2:C4="x")*1)

    would get the boolean values in numeric context as {1,1;1,0;1,0}. Now SUMPRODUCT will SUM this array and get 4.

    =SUBTOTAL(3, B2:C4) will only count if the single cell in B2:C4 is not invisible because it is filtered out. So it gets 6 when unfiltered but 4 for example, if F2 is filtered out.

    {=OFFSET(B2:C4, ROW(B2:C4) - MIN(ROW(B2:C4)), 0, 1, 1)} used in array context gets {=OFFSET(B2:C4, {2;3;4} - 2, 0, 1, 1)} = {=OFFSET(B2:C4, {0;1;2}, 0, 1, 1)} which takes B2:C4 moved {0;1;2} rows downwards, ever 0 columns sidewards, ever in height 1 and width 1 which results in {B2;B3;B4}

    So we have with SUBTOTAL

    {=SUBTOTAL(3, {B2;B3;B4})} which only counts 1 if {B2;B3;B4} is not filtered out..

    So the result of the SUBTOTAL when F2 (B3) is filtered out is: {1;0;1}.

    Within the SUMPRODUCT and out filtered row 3 we have:

    =SUMPRODUCT((B2:C4="x")*{1;0;1})

    which is {TRUE,TRUE;TRUE,FALSE;TRUE,FALSE} * {1;0;1} = {1,1;0,0;1,0} which sums to 3.

    To simplify this I would use

    =SUMPRODUCT((B2:C4="X")*SUBTOTAL(3,INDIRECT("A"&ROW(2:4))))

    which works the same way except that the INDIRECT results in {A2;A3;A4} and, because the "A" is a fixed text string, you have to change the formula if you will insert columns before column A.

    This is not necessary with the OFFSET variant because there all arguments are cell references which updates automatically when a column will be inserted.