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

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

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 <domenic22-(E-Mail Removed)>,
Domenic <(E-Mail Removed)> 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

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

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