Sum of value in cell range between 500,000 to 1,000,000

[Guest]

Dear Friends I need a formula to find numberor a sum of value ranges bewtween
500,000 to 1,000,000

 

[Rob van Gelder]

Long datatype overflowed for me - using Double instead:

Sub Test()
Dim i As Long, dbl As Double

For i = 500000 To 1000000
dbl = dbl + i
Next
MsgBox dbl
End Sub

 

[Harlan Grove]

Long datatype overflowed for me - using Double instead:

Sub Test()
Dim i As Long, dbl As Double

For i = 500000 To 1000000
dbl = dbl + i
Next
MsgBox dbl
End Sub

....

Brute force. Better to use Gauss's formula.

MsgBox 1000000 * 1000001 / 2 - 499999 * 500000 / 2

which recognizes that

Sum(500000..1000000) = Sum(1..1000000) - Sum(1..499999)

Amazing what a little math does for programming.

 

[Rob van Gelder]

I must admit I wasn't aware of Gauss's formula though suspected there must
be a quicker way - so thanks.

 

[Chip Pearson]

Try the following:

Dim Rng As Range
Dim Total As Double
For Each Rng In Range("A1:A10") '<<< CHANGE range
If Rng.Value >= 500000 And Rng.Value <= 1000000 Then
Total = Total + Rng.Value
End If
Next Rng



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Myrna Larson]

Or do it the same way you would on a worksheet, with SUMIF:

Dim Rng As Range
Dim Total As Double

Set Rng = Range("A1:A10")
With Application
Total = .SUMIF(Rng,">=500000") - .SUMIF(Rng,">100000")
End With

 

[Guest]

Dear Friends thank you for showing the interest, As I am new in this field
please also help me where can I wrote these formulas.
Thanks once again
Khawajaanwar

 

[Bob Phillips]

You can do this with worksheet formula, using the method that Gauss
(allegedly) devised as a schoolboy. This solution assumes that the first
number goes in A1, the second in A2, and allows for starting values that are
odd or even, and the last number being odd or even (this affects the
solution, because the basic method assumes an even number of entries

=IF(OR(AND(MOD(A1,2)>0,MOD(A2,2)=0),AND(MOD(A1,2)=0,MOD(A2,2)>0)),(A2+A1)*IN
T((A2-A1+1)/2),A1+(A1+1+A2)*INT((A2-(A1+1)+1)/2))

or a bit simpler

=IF(MOD(A2-A1,2),(A2+A1)*INT((A2-A1+1)/2),A1+(A1+1+A2)*INT((A2-(A1+1)+1)/2))