# IF Statement correct?

#### hensonb13

##### New Member
Looking for help on what correct excel formula to use in my situation. I think it should be an if statement but I can't get the correct amount to populate.

What I want to do is sum a range of numbers. If that range of numbers falls in between a defined set, I want to do a calculation. Below is an example

Table:
0-15 0%
15-20 10%
20-25 20%

Range:
Jan - 7
Feb - 8
Mar - 9

I'm trying to build a formula that would bring back 4.8 as the answer. Based on this example, I'm summing the three months (24) and multiplying by 20% from the table to get 4.8.
My attempt at the fomula (that isn't working) is this: =IF(SUM(D6:D8)<15,0,IF(SUM(D6:D8)>15<20,SUM(D6:D8)*0.1,IF(SUM(D6:D8)>20<25,SUM(D6:D8)*0.2,NONE)))

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Excel 2012
AB
100%
21510%
32020%
425
5
6Jan7
7Feb8
8Mar9
9
10rate:4.8
114.8
Sheet31
Cell Formulas
RangeFormula
B10=VLOOKUP(SUM(B6:B8),\$A\$1:\$B\$4,2)*SUM(B6:B8)
B11=VLOOKUP(SUM(B6:B8),{0,0;15,0.1;20,0.2;25,0},2)*SUM(B6:B8)

if you don't care to have a ref table on your sheet, you can just hard-code the array into the B10 formula as in B11.

Last edited:
Maybe

=SUM(B11:B13)*LOOKUP(SUM(B11:B13),A2:A4,B2:B4)

Code:
``````[/FONT]
[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]Table:[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="class: xl63, align: right"]10%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="class: xl63, align: right"]20%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Range:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]Jan[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]4.8[/TD]
[/TR]
[TR]
[TD="class: xl64"]Feb[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]Mar[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
[FONT=Lucida Grande]``````

Replies
0
Views
151
Replies
14
Views
1K
Replies
2
Views
254
Replies
0
Views
272
Replies
3
Views
234

1,207,290
Messages
6,077,558
Members
446,289
Latest member
excelmaster123_

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back