Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Conditional count of rows dependent on multiple columns | Excel Worksheet Functions | |||
Sum Count of Numeric Criterion | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |