Paul,

Thanks for your reply and assistance.

The data is being called from three other sheets in the workbook and extends
down 5460 cells.
The suggestion from JE McGimpsey did the trick and the correct values are
returned based on input (a number)

I'm now attempting to take it a bit further and adding functionality to the
spreadsheet.

I've added text (data) to the three sheets and I'd like to have the text be
returned on the primary sheet based on the original value input by user.
EX. If number input in 3rd field is 55 and that corresponds to "earned
grocery benefit", then I would like to have "earned grocery benefit" returned
on user input sheet.

Thanks again for your help,
John

"paul" wrote:

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