T. Valko said:Let's look at this small example...
Suppose this is your data:
...........A..........B
1....Region...Units
2........1..........46
3........1..........12
4........3..........16
5........4..........98
6........2..........92
7........3..........28
We want to be able to count how many units are >25 when the data is
filtered.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B7>25))
When the data is unfiltered the result is 4. If we filter on Region 1 then
the result is 1.
Here's how it works...
The SUBTOTAL function allows us to perform calculations on filtered data but
the SUBTOTAL function is (very) limited to the types of calculations it can
perform without some trickeration.
For this to work we need to generate an array of subtotals, one for each row
in our range. We do that using the OFFSET function. Basically, OFFSET allows
us to step through the referenced range one row at a time getting a result
from each row and generating an array of results.
In this example we're using SUBTOTAL to count how many cells in the range
are not empty. As it steps through each cell the result will be either 1 or
0. When the data is filtered the count for those visible cells that are not
empty will be1 and the count for those cells hidden by the filter or are
empty will be 0.
When the data is unfiltered (all cells visible) then we get an array of
subtotals that are all 1s. Based on the above sample data that array would
be:
{1;1;1;1;1;1}
This array is the first argument in the SUMPRODUCT function.
SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),
Let's assume we filter the data on Region 1.
...........A..........B
1....Region...Units
2........1..........46
3........1..........12
Now our array of subtotals would be:
{1;1;0;0;0;0}
Remember, 1 = cell is not empty *and* the cell is visible, 0 = cell is
either empty or the cell is not visible.
So that's how we handle the filtered data. Now we have to complete the
calculation which is to count how many units are >25.
We use the second argument of the SUMPRODUCT function to test every cell in
the range for being >25:
SUMPRODUCT(.....,--(B2:B7>25))
This will return an array of either TRUE or FALSE:
B2>25=TRUE
B3>25=FALSE
B4>25=FALSE
B5>25=TRUE
B6>25=TRUE
B7>25=TRUE
SUMPRODUCT works with numbers so we need to convert those logicals to
numbers. One way to do that is to use the double unary "--".
--(B2:B7>25)
--TRUE=1
--FALSE=0
Now we have another array of 1s and 0s:
{1;0;0;1;1;1}
SUMPRODUCT then multiplies these 2 arrays together:
......subtotals............>25.......
{1;1;0;0;0;0}* {1;0;0;1;1;1}
1*1=1
1*0=0
0*0*0
0*1=0
0*1=0
0*1=0
The results of this multiplication are summed to arrive at the final result.
=SUMPRODUCT({1;0;0;0;0;0}) = 1
When the data is filtered on Region 1:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B7>25))
=1
When the data is unfiltered:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B7>25))
=4
exp101
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.