hi
this is a follow up question to a previous thread.
with this formula (thanks to PGC01):
we get this result using the data set shown below:
As you can see, in the section of 4 rows highlighted, 120 indeed appears 5 times in total. But it only appears in two of the four rows, which would give another result of '2' instead of '5'.
i forgot to follow up on the other thread how to adjust the excellent formula shown above to not only show the aggregate total of all instances in each 'rolling' set, which it does very well, but also to count only the number of rows the target number appears in as well.
tx
this is a follow up question to a previous thread.
with this formula (thanks to PGC01):
Code:
{=MAX(COUNTIF(OFFSET($A$2:$C$11,-1+ROW(INDIRECT("1:"&ROWS($A$2:$C$11)-F4+1)),0,F4,COLUMNS($A$2:$C$11)),F3))}
we get this result using the data set shown below:
Code:
312 412 212
912 712 312 Target: 120
512 120 412 'Rolling' set of rows: 4
120 120 612 <-| Max Result: 5
212 512 112 <-|
512 412 912 <-|
120 120 120 <-|
912 512 912
612 412 112
312 120 812
As you can see, in the section of 4 rows highlighted, 120 indeed appears 5 times in total. But it only appears in two of the four rows, which would give another result of '2' instead of '5'.
i forgot to follow up on the other thread how to adjust the excellent formula shown above to not only show the aggregate total of all instances in each 'rolling' set, which it does very well, but also to count only the number of rows the target number appears in as well.
tx