Is there a way to specify a maximum sum to be added to a larger total.
Example: To qualify for an award, you must make 100 points with 5 different
catergories but there are maximum points for certain caterories. I've tried
a few things but nothing has worked so far. Anyone? Thanks.

Not much info to go by. Here's my best guess...

.............A..............B................C..
1........Cat..........Max..........Actual
2..........1.............20..............74
3..........2.............30..............61
4..........3.............40..............26
5..........4.............30..............11
6..........5.............50..............15

Try this array formula:

=SUM(IF(B2:B6=C2:C6,C2:C6,B2:B6))

Total points = 102 (20,30,26,11,15)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"RPFD531" wrote in message
...
Is there a way to specify a maximum sum to be added to a larger total.
Example: To qualify for an award, you must make 100 points with 5
different
catergories but there are maximum points for certain caterories. I've
tried
a few things but nothing has worked so far. Anyone? Thanks.