Results 1 to 3 of 3
Thread: Sum of integers (Excel2000)

20040917, 22:27 #1
 Join Date
 Jan 2001
 Location
 Wellington, Wellington, New Zealand
 Posts
 378
 Thanks
 1
 Thanked 0 Times in 0 Posts
Sum of integers (Excel2000)
Hi
I don't know much about Excel, so forgive me if this is pretty obvious. I have a range of numbers. I want to sum the INT of every item in the range (for example, INT(A1)+INT(A2)+etc etc).
How do I do this without macros, and without creating a parallel range consisting of the INT of every item in the first range?
Thanks
Dale

20040917, 23:46 #2
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Sum of integers (Excel2000)
You can get this result by summing the array of values corresponding to the INT(A1), INT(A2)... values. The trick, of course, is how to get that array!
If your data is in A1:A10, you can enter the formula:
<pre>=SUM(INT(A2:A10))
<font color=red>as an Array Formula</font color=red></pre>
Note  to enter an array formula, type the formula in the formula bar, the press ctrlaltshift to enter the formula. Excel will put curly braces around the formula to indicate that it is an array formula. Array formulas accepts arrays or cell ranges as arguments, and return arrays (or sometimes, ranges)
In this case, the "INT(A2:A10)" part of the formula is operating as an array formula, and is applying the INT function to each value in the identified range and returning the appropriate integer. The SUM function is simply taking the total of all the integers in the array that the INT function is passing to it  exactly the answer you were looking for.

20040918, 13:38 #3
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of integers (Excel2000)
=SUMPRODUCT(INT(Range))
or
=SUMPRODUCT(TRUNC(Range))
Note that INT() and TRUNC() behave differently with respect to negative values.Microsoft MVP  Excel