Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Formula (XL97:SR2)

    Is it possible to create an array formula that would add up say every fourth column. As an example there are 70 departments each having three fields (Actual, LYear, Budget) for a total of 210 columns. I would like to include in a formual the sum all department by Actual, LYear and Budget columns by row. I've attached an XL file without extending it to the 210 columns.

    Thanks,
    John
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Array Formula (XL97:SR2)

    Yes it could be done in an array formula, but it would be easier just to use SUMIF formulas. You would use the heading label to sum all Actuals, all Budgets, etc.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Array Formula (XL97:SR2)

    Try this Array formula:

    =SUM(IF(MOD(COLUMN($A$1:$G$1),2)=1,$A$1:$G$4,0))

    where you're summing the odd positioned columns (1,3, etc.) from column A to column G
    with the data in rows 1:4 as an example. (I didn't look at your attachment to see your range, but you should be able to adjust this to meet your data range)

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Columb, Cornwall, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula (XL97:SR2)

    Place the following code in to your module and decide whether you want to call the procedure manually or insert another call routine from within your project. Hope its what you want.

    KR

    Sub TotalCols()
    Dim iRow, Cnt, jRow As Integer
    Dim lAct, lLst, lPrj As Long

    iRow = 5
    jRow = 5
    Do
    jRow = jRow + 1
    Loop Until Cells(jRow, 1) = ""
    Do
    lAct = 0
    lLst = 0
    lPrj = 0
    For Cnt = 2 To 8 Step 3
    lAct = lAct + Cells(iRow, Cnt).Value
    lLst = lLst + Cells(iRow, Cnt + 1).Value
    lPrj = lPrj + Cells(iRow, Cnt + 2).Value
    Next Cnt
    Cells(iRow, Cnt + 1).Value = lAct
    Cells(iRow, Cnt + 2).Value = lLst
    Cells(iRow, Cnt + 3).Value = lPrj
    iRow = iRow + 1
    Loop Until iRow = jRow
    End Sub

Posting Permissions

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