Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    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

  2. #2
    3 Star Lounger
    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 ctrl-alt-shift 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.

  3. #3
    3 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •