Make a Spreadsheet That Can Calculate Different Ranges of Numbers

Next add in your formulas. If you are just adding fields together, pick the field you will use as your “Total” field and enter the SUM formula (the easiest way to do this is to choose it from the formula bar, then highlight the fields you want to add together)

Make the fields that show what ranges you are looking for. For example, I would have the dollar ranges:
$0 - $10,000
$10,000 - $25,000
$25,000 - $100,000
$100,000 +
I also have a field right near each range of numbers showing the interest rate I apply to that particular field. I use the decimal version of the rate, so for .25% I put in .0025.

Now you put in the following formula:

=IF(AND(B40>10000,B40<=25000),B40*(G41/G35),0)

In this example the cell B40 would be your total (yes it will even work if your “Total” cell has a formula in it!), G41 for my spreadsheet is the interest rate, G35 is a set number (365, for the number of days in the year. We do this to find the daily interest rate, but your spreadsheet may not need this). So basically, this formula says the following:
IF my “Total” is GREATER THAN 10,000 AND LESS THAN 25,000, then multiply the number in the “Total” cell by the following formula: the interest rate divided by 365, multiplied by the number in the “Total” field. The comma and the zero at the end say:

IF the number in the “Total” field is not between those two numbers, then return the value “0”. I then copy the formula and change the dollar amounts to the next tier. This way if the total falls within that range, it returns the answer I need and if it doesn’t, it gets ignored and I just get a 0. Without this formula, I would get the first tier just fine, but if the total was, say, above $25,000 then I would get an answer in the $10,000 to $25,000 range as well as the $25,000 to $100,000 range, messing my answer all up.