Formula Forensics 023. Count and Sum a Filtered List according to Criteria

Posted on June 7th, 2012 in Formula Forensics , Huis , Posts by Hui - 18 comments

Last week at the Chandoo.org Forums, TreeTopRobin, posed a question:

I need a formula in C1 that returns the number of times L appears in C7:C13
With an AutoFilter on Row 3 the value should change if I filter on a company or team
eg: If I filter on the West Team C1=2; no filter C1=3;filter on East team C1=1″

I responded with a Formula which solved TreeTopRobin’s problem:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), – -(C7:C13=B2))

So today in Formula Forensics we will examine how this works and then how it can be extended to Sum the values in other fields as well.

As always at Formula Forensics you can follow along using a Worked Example which you can download here: Download Sample File.

Count Filtered Data using Criteria

We can see that we have a small table of data which contains 4 fields, being Team, Company, Win/Loss and Score.

We can see above that the formula for Count and Sum in C15 and C16 which using the Excel Countif() and Sumif() functions returns the correct results on the unfiltered data.

However when using the Excel Sum() or Count() functions on Filtered data, these formulas ignore the Filtering and display all the values of the original table.

In the image above we have filtered the data to show only those records that the Team = East

We can see that only 1 record matches both Team = East and Win/Loss = L

To solve the Count issue I used in cell D2:
=SUMPRODUCT(SUBTOTAL(3, OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), – -(C7:C13=B2))

Which solves the problem

Lets pull it apart and see what is inside.
=SUMPRODUCT(SUBTOTAL(3, OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), – -(C7:C13=B2))
The formula is based on the Excel Sumproduct() function which we examined in Formula Forensic 007.

Sumproduct requires an Array and optional Arrays for input using the syntax:

Looking at the above formula:
=SUMPRODUCT(SUBTOTAL(3, OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), – -(C7:C13=B2))

We can see that:
Array 1: SUBTOTAL(3, OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1))
Array 2:  – -(C7:C13=B2))

Sumproduct then multiplies the result of the two array together and then sums up the products.

Important: The two Arrays represent a list of all valid Filtered Data (Array 1) and a list of all Unfiltered Data which matches the Criteria (Array 2). Hence the product of the two arrays will be an Array which contains the Filtered Data that matches the Criteria.

Array 1: SUBTOTAL(3, OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),, 1))

Array 1 uses the Excel Subtotal() function to count the number of valid entries in each location in the range.

The Syntax of Subtotal() function is:

In a spare cell G26 enter the formula =SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)) then Press F9 instead of Enter
Excel will display an Array ={1;1;0;1;1;1;1}
This represents each cell in the range C7:C13 with a 1 when there is a valid entry and a 0 when there is no text or value.

Before we move on lets see what happens when we Filter the data

Goto the Team cell A6 and Unselect all the values, then select the East team.
Go back to G26 and evaluate the  =SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)) formula
You will see that excel is now displaying ={1;0;0;1;0;0;0}
This represents the two cells which contain values when filtered that match the Filter Criteria =East

We will compare this array with the final part of the Sumproduct() formula later.

But first lets see how this works

We saw above that the syntax of the Subtotal() function is
=Subtotal( Function No, Ref 1, [Ref 2], …)

Using our formula:
=SUBTOTAL(3, OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1))

Function No: 3 = Use the Counta() function ie: count the Numbers or Text entries in Ref 1…
Ref 1: OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)

Ref 1 is an Offset() function that establishes an Array by Offsetting range C7:C13 by the row value of ROW(C7:C13)-MIN(ROW(C7:C13)) and returns a Range that is 1 cell high

And now in English?
OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)

The Offset() function offsets the range C7:C13 by the value of ROW(C7:C13)-MIN(ROW(C7:C13))
And then returns 1 cell from the new location.

If we step into the ROW(C7:C13)-MIN(ROW(C7:C13)) part,
goto a blank cell G21 and enter: =ROW(C7:C13)-MIN(ROW(C7:C13)) press F9 instead of Enter
Excel returns ={0;1;2;3;4;5;6}
This is the value of the Current Row minus the starting Row of the range C7:C13
ie: 7-7 = 0
8-7 = 1
9-7 = 2 etc

Because this is in a Sumproduct it is treated as an Array Formula and hence the Offset() function applies the array to the initial Range, One entry at a time
So:
=OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)
=OFFSET(C7:C13, {0;1;2;3;4;5;6},, 1)
So we can now see:
The Cell C7 is offset 0 Rows, 0 Columns and returns 1 cell which will be the value in C7 = W
The Cell C7 is offset 1 Rows, 0 Columns and returns 1 cell which will be the value in C8 = L
The Cell C7 is offset 2 Rows, 0 Columns and returns 1 cell which will be the value in C9 = “”

:
The Cell C7 is offset 5 Rows, 0 Columns and returns 1 cell which will be the value in C12 = L
The Cell C7 is offset 6 Rows, 0 Columns and returns 1 cell which will be the value in C13 = W

This is shown if you goto a blank cell G19 and enter =OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1) press F9 instead of Enter

Unfiltered Excel returns ={“W”;”L”;0;”L”;”W”;”L”;”W”}
Filtered Excel returns ={“W”;”L”;0;”L”;”W”;”L”;”W”}

The filtering doesn’t effect how the Offset() function performs.

The Subtotal() function =SUBTOTAL(3, OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1))
Now steps in and using Function Number 3 or Counta() adds up the number of times each value in the array is used.
Because Subtotal ignores hidden values used by Filter the value returned varies when the Filter is applied:

Unfiltered Excel returns ={1;1;0;1;1;1;1}

Filtered (Team=East) Excel returns ={1;0;0;1;0;0;0}

Finally we can see that:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), – -(C7:C13=B2))

Is equivalent to:
Unfiltered: =SUMPRODUCT({1;1;0;1;1;1;1}, – -(C7:C13=B2))
Filtered (Team = East): =SUMPRODUCT({1;0;0;1;0;0;0}, – -(C7:C13=B2))

Array 2: – – (C7:C13=B2)

Lets now move to the second Array in the Sumproduct Formula – -(C7:C13=B2)
In a spare cell G17 enter =- -(C7:C13=B2) press F9 instead of Enter
Excel will return ={0;1;0;1;0;1;0}

This is a simple array of each cell in the range C7:C13 compared to the Criteria C2  =”L”
If you enter =(C7:C13=B2) press F9 instead of Enter
Excel will return ={FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}
So you can see that Excel uses the double negative – – to multiply each value in the array

={FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE} -1 * -1

={FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE} *1

thus converting the array to
={0;1;0;1;0;1;0}

Putting It All Together

So finally we can see that:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), – -(C7:C13=B2))

Is equivalent to:
Unfiltered: =SUMPRODUCT({1;1;0;1;1;1;1},{0;1;0;1;0;1;0})
Filtered (Team = East): =SUMPRODUCT({1;0;0;1;0;0;0},{0;1;0;1;0;1;0})

Sumproduct then multiplies the two array together and sums the products

Unfiltered: =SUMPRODUCT({1*0; 1*1; 0*0; 1*1; 1*0; 1*1; 1*0})
Returning 3

Filtered (Team = East): =SUMPRODUCT({1*0; 0*1; 0*0; 1*1; 0*0; 0*1; 0*0})
Returning 1

How do we Sum instead of Count

Now we know that the logic of the arrays is to simply multiply an Array of Valid Filtered Cells by an Array of Criteria we can simply add another Array to the Sumproduct formula:

If we want to Sum the Score values in Column D we can add it as either

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),– –(C7:C13=B2),(D7:D13))

or

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), (C7:C13=B2)*(D7:D13))

Noting that the logic of which cells to include has already be dealt with by the Count function described above.

You can examine these formulas in Cell D4 in the sample file.

 

Download

You can download a copy of the above file and follow along, Download Here.

 

Formula Forensics “The Series”

This is the 23rd post in the Formula Forensics series.

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic Series

 

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.

 

 

Written by Hui...
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 Responses to “Formula Forensics 023. Count and Sum a Filtered List according to Criteria”

  1. John Sterling says:

    I'd suggest simply using the subtotal function and filtering the data using the Win/Loss column.  You get the same results and the formula is more comprehensible.

    • Hui... says:

      @John

      That is one option.

      There are times however when you want to see the whole data table or a filtered subset and still want to produce summary reports against an unfiltered field.

  2. Matthew Holbrook says:

    Is there a particular reason why you are using a comma and the unary (--) operator for the second array in the SUMPRODUCT formula?  It seems to work the same if you were to string the arrays together using the asterisk (*).  The advantage is that SUMPRODUCT treats the entire string of arrays as a single array.

  3. Kenneth Taylor says:

    Is there a way to do this on a large set of data? As in ~100,000 rows? When I try I get an error because the formula becomes too long. It says the max length of a formula is 8,192 characters. Excel 2010.

  4. RB says:

    How do I incorporate a specific text within a cell for the second array. For instance, - -(C7:C13="Apple")
    when I chose a specific text the formula does not work.

    • Hui... says:

      @RB

      I am not sure what is the issue as if I use the sample data in the post the following work fine

      Count:
      =SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), --(C7:C13="L"))
      Sum:
      =SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),(C7:C13="L")*(D7:D13))

      You may want to check that there are no leading or trailing spaces in your list of Apples

      • RB says:

        I should have given a better explanation. Heres my situation. I have a column with cells filled with names like Column 1, Column 2, Pier 1, Pier 2, etc. If the cell just contained Pier and searched for that it works. But because it has other characters in the cell its not recognizing the pier. So how can I extract specific characters of a string of text in this formula?

        Hopefully this was a better explanation

  5. MS says:

    Hello-

    This formula works pretty well for me except that it slow down excel and prevents some of my macros from working. I was wondering if there was a way to program this in VBA so that excel isn't always trying to recalculate it. I would like to use a push of a button to get it to run then paste in a cell.

    Thanks!

  6. Akshay Modi says:

    I am trying to sum filtered data in a column, but would want to ignore the negative values in the column. How to go about doing this?

  7. Bob says:

    I have this working for counting and summing, however, I have a list and for the second array, I need a criteria. That is, I'm looking for b13:b200="01.??.??" or =left((a1,2) or something like that. These types of criteria matches do not appear to work as I get a blank as a result.
    Thanks!

    • Hui... says:

      @Bob

      As your formula b13:b200=”01.??.??” looks like you are trying to check the first day of the month of the range
      What about trying Day(B13:B200)=1

  8. Sricharan says:

    Hai Experts,
    i understood this formula well and working fine in MS Excel 2013
    but when the same am trying to place in google Spreadsheet it shows error as
    "SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2014, column count: 1." and as a result #VALUE! Appears in cell.
    Can anyone please help me how would i get it done in Google Spread sheet
    or is there any other formula as a substitute for this.
    Thank you very much.

  9. vivek says:

    thanks for providing this.. but why does excel keeps on prompting Circular referencing in cell D3?

    • Hui... says:

      @Vivek

      I don't know

      I just downloaded the file and it is working fine and not showing that error

      Goto the Formulas, Calculation Options Tab and check that Calculation is set to Automatic

      What version of Excel and Windows are you using ?

Leave a Reply