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

Sum Count of Numeric Criterion for LAST 5 Rows
 

This is looking in col A for text it can't find to find the last
rowsubtracting 4and 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

"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

Sum Count of Numeric Criterion for LAST 5 Rows
 

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

Sum Count of Numeric Criterion for LAST 5 Rows
 

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

Sum Count of Numeric Criterion for LAST 5 Rows
 

but Doms is better

--
Don Guillett
SalesAid Software

"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

Sum Count of Numeric Criterion for LAST 5 Rows
 

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