Count Filtered Visible Items that Match Numeric Criteria between two ranges
 

Hi All,

I'm using the Formula below to give me a static count of numeric values that
fall within a specified range; say 50-75 inclusive. These ranges are housed
in cell X$1. The Named Range "Data" is a dynamic 9 column range spanning many
rows. Named Range "Data" is defined as
=OFFSET(Sheet1!$H$15,0,0,COUNT(Sheet1!$H:$H),9)

Static Count:
=COUNTIF(Data,"="&LEFT(X$1,2))-COUNTIF(Data,""&RIGHT(X$1,2))

Based on the above can anyone provide a Formula that provides a Dynamic Count
when "Data" is filtered.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

Count Filtered Visible Items that Match Numeric Criteria between two ranges
 

Try...

=SUM(IF(SUBTOTAL(3,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1)),IF(Data=LE
FT(X$1,2),IF(Data<=RIGHT(X$1,2),1))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <66940ec24c9a0@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

I'm using the Formula below to give me a static count of numeric values that
fall within a specified range; say 50-75 inclusive. These ranges are housed
in cell X$1. The Named Range "Data" is a dynamic 9 column range spanning many
rows. Named Range "Data" is defined as
=OFFSET(Sheet1!$H$15,0,0,COUNT(Sheet1!$H:$H),9)

Static Count:
=COUNTIF(Data,"="&LEFT(X$1,2))-COUNTIF(Data,""&RIGHT(X$1,2))

Based on the above can anyone provide a Formula that provides a Dynamic Count
when "Data" is filtered.

Thanks
Sam

Count Filtered Visible Items that Match Numeric Criteria between two ranges
 

Actually, since Data contains numeric values, the values returned by
both the LEFT and RIGHT functions need to be coerced into numerical
values. Therefore...

LEFT(X$1,2)

should be

LEFT(X$1,2)+0

and

RIGHT(X$1,2)

should be

RIGHT(X$1,2)+0

Hope this helps!

In article ,
Domenic wrote:

Try...

=SUM(IF(SUBTOTAL(3,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1)),IF(Data=LE
FT(X$1,2),IF(Data<=RIGHT(X$1,2),1))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <66940ec24c9a0@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

I'm using the Formula below to give me a static count of numeric values
that
fall within a specified range; say 50-75 inclusive. These ranges are housed
in cell X$1. The Named Range "Data" is a dynamic 9 column range spanning
many
rows. Named Range "Data" is defined as
=OFFSET(Sheet1!$H$15,0,0,COUNT(Sheet1!$H:$H),9)

Static Count:
=COUNTIF(Data,"="&LEFT(X$1,2))-COUNTIF(Data,""&RIGHT(X$1,2))

Based on the above can anyone provide a Formula that provides a Dynamic
Count
when "Data" is filtered.

Thanks
Sam

Count Filtered Visible Items that Match Numeric Criteria between two ranges
 

Hi Domenic,

Thanks for reply. The Formula is not returning the desired result. It
returns zero.

Further help appreciated.

Cheers,
Sam



Domenic wrote:
Try...

=SUM(IF(SUBTOTAL(3,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1)),IF(Data=LE
FT(X$1,2),IF(Data<=RIGHT(X$1,2),1))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

--
Message posted via http://www.officekb.com

Count Filtered Visible Items that Match Numeric Criteria between two ranges
 

Hi Domenic,

Sorry, didn't see your amendment before I posted. The Formula works fine with
this amendment.

Thanks again for your assistance.

Cheers,
Sam

Domenic wrote:
Actually, since Data contains numeric values, the values returned by
both the LEFT and RIGHT functions need to be coerced into numerical
values. Therefore...

LEFT(X$1,2)

should be

LEFT(X$1,2)+0

and

RIGHT(X$1,2)

should be

RIGHT(X$1,2)+0

Hope this helps!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1