Summing Non Contiguous Cells in a Row or Column

[Joe Ottenhof]

I need to total a large number of cells in a column that are not contiguous, ie, not next to each other. For example I want the total of every 4th cell in a column without having to manually entering a formula containing the exact cell references...that would be way to tedious and time consuming.

[Kevin@Radstock]

Hi Joe Ottenhof

Two methods assuming data is in A1:A1000:

1/ =SUMPRODUCT(--(MOD(ROW(A1:A1000),4)=0),A1:A1000)

2/ =SUM((MOD(ROW(A1:A1000),4)=0)*(A1:A1000))
This is an array formula: CTRL + SHIFT + ENTER

You can change what rows to sum, by changing the MOD part ie: =0 to = 1 or 2 etc

Kevin

[Joe Ottenhof]

Originally Posted by Kevin@Radstock

Hi Joe Ottenhof

Two methods assuming data is in A1:A1000:

1/ =SUMPRODUCT(--(MOD(ROW(A1:A1000),4)=0),A1:A1000)

2/ =SUM((MOD(ROW(A1:A1000),4)=0)*(A1:A1000))
This is an array formula: CTRL + SHIFT + ENTER

You can change what rows to sum, by changing the MOD part ie: =0 to = 1 or 2 etc

Kevin

Kevin
Thanks very much. The second way works perfectly. The first one gives me an error. I paid a guy $38 and he didn't get the answer...so once again thanks,

[Joe Ottenhof]

Sorry Kevin...the first one does work as well. They both work thanks once again.

Joe O

[Kevin@Radstock]

Hi Joe

Thanks for the feed back, glad I was of some help.

Kevin