Need a f-la to sum cumulative values

[Sigizmund]

Hi dear friends,

Here is my case. I have intervals bands in columns A and
B, e.g. 20,000 – 25,000 25,000 – 30,0000 and so on.
There is a corresponding list of values in column C.

I know I could use Vlookup (21111, A1:C20,3,true) to
lookup the corresponding value. However, I need my
formula to sum all the values (cumulative) in column C
from C1 to the match.

Any great ideas?

Thanks in advance,

Sigizmund Mudakin

 

[Frank Kabel]

Hi Sigizmund
try the following
=SUM(OFFSET($C$1,0,0,MATCH(21111,A1:A20)))

HTH
Frank

 

[Sigizmund]

Thank you very much, Frank
Works like a charm!

I am just wondering if there a way to simplify the
following

=SUM(OFFSET($C$1,0,0,MATCH(21111,A1:A20)))-SUM(OFFSET
($C$1,0,0,MATCH(5333,A1:A20)))

I can'f figure out how to restrict it within one sum
statement.

It is fine if you don't know, the above is good enough.

Thanks again,
Sigizmund Mudakin

 

[Frank Kabel]

Hi Sigizimund
try
=SUM(OFFSET($C$1,MATCH(5333,A1:A20,0)-1,0,MATCH(21111,A1:A20,0)))
I also added the third parameter for MATCH (to look for exact matches)

HTH
Frank