ahhh i see i replicated your ranges and after the third or so the "if" value
was 0.I think you make a little table with your values
A B
1 =bronze!B1
2 =sum(bronze!B1:B5)
3 =sum(bronze!B21:B84)
4 =sum(bronze!B85:B340)
5 =sum(bronze!B341:B1364)
6 =sum(bronze!B1365:B5460)

and use =Vlookup(L21,bronze!A1:B6,2,false)

--
paul
remove nospam for email addy!



"Jackanorry" wrote:

After OzziJC reply, I went back to the IF function.

Using the following does return the correct value for the first calculation.
=IF(L21=1,SUM(Bronze!B1),IF(L21=2,SUM(Bronze!B1:B2 ),IF(L21=3,SUM(Bronze!B1:B3),IF(L21=4,SUM(Bronze!B 1:B4)))))

It will not however work in the second to sixth calculation, do to the
number of "allowed' calculations - the number of values a
The second range of numbers 5 - 20
The third range of number 21 - 84
The fourth range of number 85 - 340
The fifth range of numbers 341 - 1364
The sixth range of numbers 1365 - 5460

I've attempted to write a nested formula and an array formula - neither
which have worked - yet!

Once again any help/guidance would be greatfully appreciated.

John