# Sum Range

#### andy_2610

##### Board Regular
Hi all,

I want to sum a range once the range is greater than a certain amount.

For an example in column D starting in D2
Once the sum of "20% of sales" is greater than 10, I want it to sum anything afterwards in Column A. I want to be able to drag that down and as soon it is greater than 10 in cells D5 will be 10 and D6 will be 30.

My formula was =SUMIF(sum(\$B\$2:B2),">10",\$A\$2:A2)

 A B C D 1 SALES 20% of sales 2 10 2 3 20 4 4 20 4 5 10 2 6 20 4

<tbody>
</tbody>

Andrew

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Marcelo Branco

##### MrExcel MVP
Not sure if i understand what you need. See if this is Ok

Formula in D2 copied down
=IF(SUM(B\$2:B2)>10,SUM(D1,A2),"")

M.

#### steve the fish

##### Well-known Member
Does this do it? In D2:

=IF(SUM(\$B\$2:B2)>10,A2+D1,0)

#### Shrikant

##### Active Member
Put in D2

=B2

put in D3 [ then press enter and drag down to copy]

=IF(D2<10,SUM(D2,B3),SUM(D2,A3))

Last edited:

#### andy_2610

##### Board Regular
Not sure if i understand what you need. See if this is Ok

Formula in D2 copied down
=IF(SUM(B\$2:B2)>10,SUM(D1,A2),"")

M.

Hey M, can you explain the "SUM(D1,A2)" for me?

Thanks,

Andrew

#### Marcelo Branco

##### MrExcel MVP
It is the same as D1+A2 but with the advantage of not generating an error when D1 contains a text (a header for example), not a number.

M.

#### andy_2610

##### Board Regular
How do I do that without using a drag down formula? So after it reaches >=10 it will start adding afterwards so we will have a sum of 6. (So I know it equals 10 up in the example, but say it crosses 10 and it equals 11, how do I split that "1" away to add on with the others after?)

Replies
1
Views
662
Replies
3
Views
889
Replies
8
Views
967
Replies
0
Views
327
Replies
0
Views
661

1,195,650
Messages
6,010,925
Members
441,573
Latest member
Goronvir

### 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