Tables with criteria that adds up amounts

[Chris]

Hi,

Aside from a very complicated IF formula, does anyone know
if I have a criteria such as 3 mentioned in my example
below where it can add the amounts from Criteria 1 to
Criteria 3. I did think of VLookups and Sumifs formula's
but would come up to $80k instead of the $150k I am
looking for. Hope this all makes sense. Or if the IF
formula is the only way to go, how would you set it up.
Please assume that the number of criteria would be a lot
higher than the four I use in this example. Thanks for
your help!

Example

Criteria Amount
1 20,000.00
2 50,000.00
3 80,000.00
4 100,000.00


Answer:
3 150,000.00

 

[Peo Sjoblom]

One possible way

=SUM(OFFSET($B$1,,,3))

or better

=SUM(OFFSET($B$1,,,C2))

where C2 holds the criteria so if you put 4 there your example would return

250,000.00