Hi

Sorry to say but that is beyond my expertise, I'm sure some people are
already working on that so be patient and good luck.

Regards!
Jean-Guy


"been dribbled to 2007" wrote:

almost ,Sir

yet i need to clarify that
need a formula, to replace the formulas of Col.D without a helper column
COL.C.

please add..."i need a single formula, without building a list under Col D.
this may look like a calculator...".
..in mySheet1: The Col.A and Col.B <10000 rows has a value in series and
its hidden. Due to a long list of values which will be fed intermittently
with data, i need to avoid long calculation time.
the user will just feed in two data "c and 1460" and the formula must be
able to gave the cumulative sum.

hope that your formula can be a little bit adjusted to suit my request.
thanks again
dribler2


"pinmaster" wrote:

Hi,

Please disregard my first post, there was some problem with the formula. Try
this instead:

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

again copy down one cell and add the cell above it to the end of the
formula, you can also replace the 0 in the middle with the cell above:

e.g....first formula is in D16 then in D17
=IF(ISNA(SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))),D16,SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0))))+D16

adjust to suit.

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