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.
0 comments:
Post a Comment