Sum Range

[andy_2610]

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>

Thanks in advance,

Andrew

 

[Marcelo Branco]

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]

Does this do it? In D2:

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

 

[Shrikant]

Put in D2

=B2

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

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

 

[andy_2610]

ADVERTISEMENT

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]

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]

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?)