Formula to sum every nth column in another worksheet

[jeffdolton]

Hi, In sheet 2 I wish to sum every 5th column in Sheet 1 starting at a specific cell address. I've tried a few formula on the net but these haven't worked and some have been overly long. Is there a reasonably short formula for this please or vba function.

Thanks

 

[Saba Sabaratnam]

Hi

Assuming that your Sheet 1 will look like the following and you want to add column 5, 10 and 15 for range highlighted in yellow (sheet2!A2:O4).

Set up Sheet 2 in the format below.

and enter the following array formula in B3 by pressing Control + Shift + Enter and copy the formula across to cell D3.

=SUM(INDEX(Sheet2!$A$2:$O$4,,MATCH(Sheet1!B$1,COLUMN(Sheet2!$A$1:$O$1),0)))


Kind regards

Saba

 

[jeffdolton]

Hi

Assuming that your Sheet 1 will look like the following and you want to add column 5, 10 and 15 for range highlighted in yellow (sheet2!A2:O4).

View attachment 29243

Set up Sheet 2 in the format below.

View attachment 29244

and enter the following array formula in B3 by pressing Control + Shift + Enter and copy the formula across to cell D3.

=SUM(INDEX(Sheet2!$A$2:$O$4,,MATCH(Sheet1!B$1,COLUMN(Sheet2!$A$1:$O$1),0)))


Kind regards

Saba

Sorry Saba, I didn't explain my request very well. In Sheet 1 I need to add E2, J2 and O2 so a total of 115.

Regards, Jeff

 

[Saba Sabaratnam]

Hi Jeff,

Assuming that your Sheet 1 will look like the following with Column No in Row 1

in Sheet 2, enter the following array formula in C3 by pressing Control + Shift + Enter and copy down the formula to B5.

=SUM(SUMIFS(Sheet2!2:2,Sheet2!$1:$1,Sheet1!$C$1:$E$1))

Kind regards

Saba

 

[jeffdolton]

Hi Jeff,

Assuming that your Sheet 1 will look like the following with Column No in Row 1

View attachment 29249

in Sheet 2, enter the following array formula in C3 by pressing Control + Shift + Enter and copy down the formula to B5.

=SUM(SUMIFS(Sheet2!2:2,Sheet2!$1:$1,Sheet1!$C$1:$E$1))


View attachment 29250

Kind regards

Saba

Thanks Saba, I set up a blank excel sheets exactly as above and the formula returned a value of 0. Also, my equivalent of Sheet 1 has column headings that can't be changed as these are generated from code.