Max sums

[RPFD531]

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.

 

[T. Valko]

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)