Hi,

The #N/A is cause when a letter in the second table is not present in the
first, my last formula should take care of that.

as for:
LOOKUP(A16,{"a","b","g"},{6,-1,-6})) .
LOOKUP(A16,{"a","b","g"},{6,1,-6})

I had parts of this wrong, again the last formula I gave should resolve
this. Each letter has its own code, for ex. the letter "a" is 97, "b" is 98
and so on....and this tell excel how much to add or deduct from the code
depending on the letter it is looking at,and the CHAR function turns those
codes into letters.

As I said, I'm not an expert so there may be a better solution.

HTH
Jean-Guy


"been dribbled to 2007" wrote:

thank you i have seen results (0 and #N/A), yet not so quite sure if i does
it correctly
the table i gave is a sample

its my first time to see such formula, hence its not easy to understand it...

LOOKUP(A16,{"a","b","g"},{6,-1,-6})) .
LOOKUP(A16,{"a","b","g"},{6,1,-6})

what does the above do? 2 lookups <first is "-1" and the next is "1"...
and how to avoid #N/A result..

thank you for your quick response,
dribler2





"pinmaster" wrote:

Hi

I'm far from a genius but maybe something like this:

assuming A5:B11 is your first table and A16 is the first cell in your 2nd
table

=SUMPRODUCT((VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a ","b","g"},{6,-1,-6})),$A$5:$B$11,2,0)=B16-1)*(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g" },{6,1,-6})),$A$5:$B$11,2,0)=B16+1))

copy down 1 cell and add the previous cell result to the end of the formula
then copy down as far as needed.

HTH
Jean-Guy



"been dribbled to 2007" wrote:

happy new year, Sirs,

i am looking again for another genius to help me...

please help me to find any formula to solve this PROGressive count for
my2007 workbook.
i need to count in a cumulative manner the mid-match of 2 column-row data in
COL.A and COL.B.
-----------------------------------------------
Table 1: Reference for lookup/ mid-match
COL.A COL.B COL.C COL.D

b 2
d 4
f 6
a 8
c 10
------------------------------------------------------------------------------------------
Table 2: Value to lookup for mid-match
A B C D

a 1 =count = 0
b 2 =count = 0
c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4]
d 4 =count = 1
e 5 1 =count = 2 [ cum. sum]
f 6 =count = 2
g 7 1 =count = 3
a 8 =count = 3
b 9 1 =count = 4
c 10 =count = 4
d 11 =count = 4
e 12 =count = 4
f 13 =count = 4
g 14 =count = 4

need a formula, to replace the formulas of Col.D without a helper column
COL.C.
Note Col.C is either 1 or a blank result.
Table 2 Col.A is a looping series from a to g.
Col.B filled with a series of numeric or date value.

thanks for reply with any smart or long formula or whatever merged formulas.
best regards,
been dribled to 2007