is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

Hi,
take a lood at Debra web

http://www.contextures.com/xlFunctions04.html#Filter

"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

Hi Shane,
the formula worked after I added another closure to the parentheses, but did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
Hi Shane,
the formula worked after I added another closure to the parentheses, but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
Hi Shane,
the formula worked after I added another closure to the parentheses, but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
Hi Shane,
the formula worked after I added another closure to the parentheses,
but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

Hey, this works great for something I'm doing as well. But I can't
understand how this works. Is there any chance you could explain what's
going on with this formula? I don't get it.

Thanks,

Jay

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
Hi Shane,
the formula worked after I added another closure to the parentheses,
but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

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

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

This will return an array of either TRUE or FALSE:

B225=TRUE
B325=FALSE
B425=FALSE
B525=TRUE
B625=TRUE
B725=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:B725)

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

=1

When the data is unfiltered:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B725))

=4


exp101
--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
Hey, this works great for something I'm doing as well. But I can't
understand how this works. Is there any chance you could explain what's
going on with this formula? I don't get it.

Thanks,

Jay

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
Hi Shane,
the formula worked after I added another closure to the parentheses,
but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

Excellent! Thank you.

"T. Valko" wrote:

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

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

This will return an array of either TRUE or FALSE:

B225=TRUE
B325=FALSE
B425=FALSE
B525=TRUE
B625=TRUE
B725=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:B725)

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

=1

When the data is unfiltered:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B725))

=4


exp101
--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
Hey, this works great for something I'm doing as well. But I can't
understand how this works. Is there any chance you could explain what's
going on with this formula? I don't get it.

Thanks,

Jay

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
Hi Shane,
the formula worked after I added another closure to the parentheses,
but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

You're welcome!

--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
Excellent! Thank you.

"T. Valko" wrote:

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

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

This will return an array of either TRUE or FALSE:

B225=TRUE
B325=FALSE
B425=FALSE
B525=TRUE
B625=TRUE
B725=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:B725)

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

=1

When the data is unfiltered:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-ROW(B2),,1)),--(B2:B725))

=4


exp101
--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
Hey, this works great for something I'm doing as well. But I can't
understand how this works. Is there any chance you could explain
what's
going on with this formula? I don't get it.

Thanks,

Jay

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP


"Mr E" wrote in message
...
Hi Shane,
the formula worked after I added another closure to the
parentheses,
but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

is it possible to use countif in a range that has been
filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

really good formulla for filtered countif. Can it work for sumif also?



T. Valko wrote:

Try
05-Aug-09

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:

On Wednesday, August 05, 2009 9:06 AM
Mr wrote:

using countif in a filtered range
is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

On Wednesday, August 05, 2009 9:33 AM
Eduard wrote:

using countif in a filtered range
Hi,
take a lood at Debra web

http://www.contextures.com/xlFunctions04.html#Filter

"Mr E" wrote:

On Wednesday, August 05, 2009 10:42 AM
ShaneDevenshir wrote:

using countif in a filtered range
Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

On Wednesday, August 05, 2009 12:35 PM
Mr wrote:

Hi Shane,the formula worked after I added another closure to the parentheses,
Hi Shane,
the formula worked after I added another closure to the parentheses, but did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

On Wednesday, August 05, 2009 1:42 PM
T. Valko wrote:

Try
Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP

On Wednesday, August 05, 2009 2:18 PM
Mr wrote:

using countif in a filtered range
I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

On Wednesday, August 05, 2009 2:31 PM
T. Valko wrote:

You're welcome. Thanks for the feedback!
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

On Thursday, August 27, 2009 3:51 PM
Jay wrote:

Hey, this works great for something I'm doing as well.
Hey, this works great for something I am doing as well. But I cannot
understand how this works. Is there any chance you could explain what is
going on with this formula? I do not get it.

Thanks,

Jay

"T. Valko" wrote:

On Thursday, August 27, 2009 10:31 PM
T. Valko wrote:

using countif in a filtered range
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:B725))

When the data is unfiltered the result is 4. If we filter on Region 1 then
the result is 1.

Here is 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 are 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 is 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:B725))

This will return an array of either TRUE or FALSE:

B225=TRUE
B325=FALSE
B425=FALSE
B525=TRUE
B625=TRUE
B725=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:B725)

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

On Friday, August 28, 2009 9:36 AM
Jay wrote:

using countif in a filtered range
Excellent! Thank you.

"T. Valko" wrote:

On Friday, August 28, 2009 11:12 AM
T. Valko wrote:

You're welcome!
You're welcome!

--
Biff
Microsoft Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
Viewing Tech-Ed 2005 Content for Non-Attendees
http://www.eggheadcafe.com/tutorials...2005-cont.aspx

Can it work for sumif also?

Yes. How about providing some details of what you want to do.

--
Biff
Microsoft Excel MVP


<S Dey wrote in message ...
really good formulla for filtered countif. Can it work for sumif also?



T. Valko wrote:

Try
05-Aug-09

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:

On Wednesday, August 05, 2009 9:06 AM
Mr wrote:

using countif in a filtered range
is it possible to use countif in a range that has been filtered?
my formula seems to be counting all data in the range.
=COUNTIF(Q11:Q301,"249")

your help would be greatly appreciated

On Wednesday, August 05, 2009 9:33 AM
Eduard wrote:

using countif in a filtered range
Hi,
take a lood at Debra web

http://www.contextures.com/xlFunctions04.html#Filter

"Mr E" wrote:

On Wednesday, August 05, 2009 10:42 AM
ShaneDevenshir wrote:

using countif in a filtered range
Hi,

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(11:301)-1,,1))*(Q11:Q301249)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mr E" wrote:

On Wednesday, August 05, 2009 12:35 PM
Mr wrote:

Hi Shane,the formula worked after I added another closure to the
parentheses,
Hi Shane,
the formula worked after I added another closure to the parentheses, but
did
not return the proper answer. any idea?

"Shane Devenshire" wrote:

On Wednesday, August 05, 2009 1:42 PM
T. Valko wrote:

Try
Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Q11:Q301,ROW(Q11:Q30 1)-ROW(Q11),,1)),--(Q11:Q301249))

--
Biff
Microsoft Excel MVP

On Wednesday, August 05, 2009 2:18 PM
Mr wrote:

using countif in a filtered range
I would not have a clue why that works, but it does. Thanks

"T. Valko" wrote:

On Wednesday, August 05, 2009 2:31 PM
T. Valko wrote:

You're welcome. Thanks for the feedback!
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

On Thursday, August 27, 2009 3:51 PM
Jay wrote:

Hey, this works great for something I'm doing as well.
Hey, this works great for something I am doing as well. But I cannot
understand how this works. Is there any chance you could explain what is
going on with this formula? I do not get it.

Thanks,

Jay

"T. Valko" wrote:

On Thursday, August 27, 2009 10:31 PM
T. Valko wrote:

using countif in a filtered range
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:B725))

When the data is unfiltered the result is 4. If we filter on Region 1 then
the result is 1.

Here is 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 are 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 is 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:B725))

This will return an array of either TRUE or FALSE:

B225=TRUE
B325=FALSE
B425=FALSE
B525=TRUE
B625=TRUE
B725=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:B725)

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

On Friday, August 28, 2009 9:36 AM
Jay wrote:

using countif in a filtered range
Excellent! Thank you.

"T. Valko" wrote:

On Friday, August 28, 2009 11:12 AM
T. Valko wrote:

You're welcome!
You're welcome!

--
Biff
Microsoft Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
Viewing Tech-Ed 2005 Content for Non-Attendees
http://www.eggheadcafe.com/tutorials...2005-cont.aspx