Adding up amounts in a table with criteria

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
Hi,

I suggest you use =DSUM, even though it is a little
cumbersome since it requires two extra rows for criteria.

Please refer to Xl Help.

Regards,
Puzatiy Popandopolo
 
Hi Chris
if I understood you correctly you want to sum all amounts for which the
criteria <=your search_value. Try
=SUMIF($A$1:$A$10,"<=3",$B$1:$B$10)
or if your criteria is stored in a cell (lets say C1) use
=SUMIF($A$1:$A$10,"<=" & C1,$B$1:$B$10)

Frank
 
Back
Top