CODEWITHSUNDEEP
excelexcel-formulaformula
Ed Briscoe
Ed Briscoe

Reputation: 117

How to add every nth cell in a column in Excel?

I have 2 spreadsheets.

Spreadsheet 1: has a number in every 6th row in the A column Spreadsheet 2: I want to add every number in every 6th cell in the A column into a cell on this spreadsheet

Upvotes: 1

Views: 8734

Answers (4)

Luke Kot-Zaniewski
Luke Kot-Zaniewski

Reputation: 1161

I think sumproduct is a nifty solution for this. We multiply the values we want to ignore by 0 and the ones we want to keep by one.

=SUMPRODUCT(--(MOD(ROW(A1:A16), 6)=0), A1:A16)

Upvotes: 1

skkakkar
skkakkar

Reputation: 2828

In case You would like to go for Non Array Formula and as I understand you want to add every 6 th value. You can use the following formula on sheet2 column B1 as a helper column and fill down by simple enter only. I am assuming your data in column A of both the sheets.

 =IF(MOD(ROW($A1),6)+MOD(ROW(Sheet1!$A1),6)=0,A1+Sheet1!$A1," ")

Snapshot shows the results obtained.

Snapshot showing results

In case you want total sum other answers have covered that.

Upvotes: 1

timthebomb
timthebomb

Reputation: 230

You could use a VBA macro to accomplish this. 

Sub everySixth()
 Dim f As String
 f = "=Sheet1!A6"
 Dim i As Long

 For i = 1 To 5
  f = f & "+Sheet1!A" & (i * 6)
 Next

 ActiveCell.formula = f
End Sub

Upvotes: 0

Mrig
Mrig

Reputation: 11702

Enter the following formula in any cell of Sheet2:

=SUM(Sheet1!A1:A60*(MOD(ROW(Sheet1!A1:A60),6)=0))

This is an array formula so commit by pressing Ctrl+Shift+Enter.

Change range A1:A60 as required.

Upvotes: 3

Related Questions