Sum Count of Numeric Criterion for LAST 5 Rows

Hi All,

I would like a Formula that uses an Input cell (to accommodate changing
Numeric Criterion) Summing the Count of a Numeric Criterion in the LAST 5
Rows of a Dynamic Named Range "Data". Named Range "Data" spans 8 Columns and
many Rows.

Thanks
Sam

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

This is looking in col A for text it can't find to find the last
row>subtracting 4>and summing that range for col B
=SUM(INDIRECT("b"&MATCH("zzzzz",A:A)-4&":b"&MATCH("zzzzzzz",A:A)))
this counts col B over 2
=COUNTIF(INDIRECT("b"&MATCH("zzzzz",A:A)-4&":b"&MATCH("zzzzzzz",A:A)),">2")
name num 20
a 1 4
b 2
c 3
d 4
e 5
f 6


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:5ef5eaa85a0ae@uwe...
> Hi All,
>
> I would like a Formula that uses an Input cell (to accommodate changing
> Numeric Criterion) Summing the Count of a Numeric Criterion in the LAST 5
> Rows of a Dynamic Named Range "Data". Named Range "Data" spans 8 Columns
> and
> many Rows.
>
> Thanks
> Sam
>
> --
> Message posted via http://www.officekb.com

Try...

=COUNTIF(INDEX(Data,ROWS(Data)-4,1):INDEX(Data,ROWS(Data),0),Criterion)

Hope this helps!

In article <5ef5eaa85a0ae@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi All,
>
> I would like a Formula that uses an Input cell (to accommodate changing
> Numeric Criterion) Summing the Count of a Numeric Criterion in the LAST 5
> Rows of a Dynamic Named Range "Data". Named Range "Data" spans 8 Columns and
> many Rows.
>
> Thanks
> Sam

Hi Don,

Thank you very much for your time and assistance - explanation of Formulas
much appreciated.

Cheers,
Sam

>Don Guillett wrote:
>This is looking in col A for text it can't find to find the last row subtracting 4 and summing that range for col B
>=SUM(INDIRECT("b"&MATCH("zzzzz",A:A)-4&":b"&MATCH("zzzzzzz",A:A)))

>this counts col B over 2
>=COUNTIF(INDIRECT("b"&MATCH("zzzzz",A:A)-4&":b"&MATCH("zzzzzzz",A:A)),">2")

> name num 20
> a 1 4

> b 2
> c 3
> d 4
> e 5
> f 6

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

but Doms is better

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:5ef86900f7a7c@uwe...
> Hi Don,
>
> Thank you very much for your time and assistance - explanation of Formulas
> much appreciated.
>
> Cheers,
> Sam
>
>>Don Guillett wrote:
>>This is looking in col A for text it can't find to find the last row
>>subtracting 4 and summing that range for col B
>>=SUM(INDIRECT("b"&MATCH("zzzzz",A:A)-4&":b"&MATCH("zzzzzzz",A:A)))
>
>>this counts col B over 2
>>=COUNTIF(INDIRECT("b"&MATCH("zzzzz",A:A)-4&":b"&MATCH("zzzzzzz",A:A)),">2")
>
>> name num 20
>> a 1 4
>
>> b 2
>> c 3
>> d 4
>> e 5
>> f 6
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200604/1

Hi Domenic,

Thank you very much. Formula worked Great!

Don Guillet also provided a method for a solution. However, Your Formula
syntax gives me a clearer image.

Cheers,
Sam

Domenic wrote:
>Try...
>
>=COUNTIF(INDEX(Data,ROWS(Data)-4,1):INDEX(Data,ROWS(Data),0),Criterion)
>
>Hope this helps!

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