Sum Count of Numeric Criterion for LAST 5 Rows

[Sam via OfficeKB.com]

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

 

[Don Guillett]

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

 

[Domenic]

Try...

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

Hope this helps!

 

[Sam via OfficeKB.com]

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

 

[Don Guillett]

but Doms is better

 

[Sam via OfficeKB.com]

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