Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Sum (2002 SP-2)

    When you select a range of cells with the cursor, Excel will show you the sum of the selected cells on the status bar. Is there a way to do this same thing with a formula or code?

    Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Sum (2002 SP-2)

    For a formula, I usually highlight the range, extend it one row or column or both, and press "Alt-=" which provides the Sum of the selected Columns/Rows. If you want a single formula covering multiple rows and columns, the form is:

    =SUM(A1:Z2000).

    For VBA, it's easiest to use:

    Application.WorkSheetFunction.Sum(A1:Z2000)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Sum (2002 SP-2)

    Thanks for the information. However, I am not looking to sum on a fixed range and I don't quite know how to word what I am looking to do. I want to highlight an area and have the sum in a fixed cell(say M2) outside of the selected range(say A3:C12). I could then select a different range(B6:H9) and have the sum displayed in the same fixed cell(M2). I guess what I need to know is can you reference a range selected with the cursor in a formula or in code?

    Thank you.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range Sum (2002 SP-2)

    For your example try

    [M2] = Application.WorksheetFunction.Sum(Selection)

    If you want the code to run each time a selection is made the following might help :

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
    [M2] = Application.WorksheetFunction.Sum(Selection)
    Else
    [M2] = ""
    End If
    End Sub

    To use this code, right click on the trelevant sheet tab, select vVew Code and place the above (amended to suit) in the code window.

    Andrew C

  5. #5
    New Lounger
    Join Date
    Jul 2003
    Location
    Phoenix, Arizona, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Sum (2002 SP-2)

    Perfect! Thank you.

Posting Permissions

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