1. ## 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

2. ## 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. ## 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. ## 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
•