Max sums
 

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.

Max sums
 

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.