Excel Voodoo: Adding Every Nth Row/Column Together [With Breakdown]

August 15, 2014 Alex Hill

Ever wanted to add every Nth column together in Excel?

Easy! but what is going on here? I'll post the technical explanation below, but all you need to know is the last two numbers are the key to the whole thing!

Changing the 3

Fill in the blank: "I want to add every ____th cell together." Whatever is in that blank, that should be the number you use instead of 3.

Changing the 1

The 3 divides the cells into blocks of 3, but then you have to tell Excel which number to look at in each block. There is a lot of technical work behind this (see below) but in the interest of keeping life simple, most small formulas can be guess-and-checked.

The number replacing 1 will always be less than the number replacing 3. Start with 0 and look at the result. Is that number close to what you think the result should be?

The alternative (more reliable) method is to take the first cell the formula is searching for (in this case D10) and divide that number (10) by the number replacing 3. Now time for some long division! 3x3=9, so when dividing by 10 there is a remainder of 1. If you wanted the formula to start on D10 the last number in the formula should be 1. Starting on D11 should be 2, and D12 should be 0 (Because there is not a remainder!). Sounds crazy right? That's because this is pretty much Excel Voodoo as far as the average Joe is concerned.

the row function of coarse returns the row # of a given cell reference. D10, row = 10... Simple enough. By putting it in sumproduct ROW(D10:D249) creates an array of row #s {10,11,12,13...etc...,249} Mod returns the remainder after a devision. Examples, MOD(10,3) = 1 because.. 3 can be devided into 10 3 times 3X3 = 9 10-9 = 1 MOD(11,3) = 2 3 can be devided into 11 3 times 3X3 = 9 11-9 = 2 MOD(12,3) = 0 3 can be devided into 12 4 times 3X4 = 12 12-12 = 0 so MOD({10,11,12,13...etc...,249},3) becomes an array of the results of each mod. {1,2,0,1,2,0,1,2,0...etc} So now you have =SUMPRODUCT($D$10:$D$249*({1,2,0,1,2,0,1,2,0...etc}=1)) Then it tests if each result of the mod = 1, and returns TRUE or False for each =SUMPRODUCT($D$10:$D$249*({True,false,false,true,false,false,etc..})) Then multiplying that array of true/false results by the numbers in D10:D249 coerces the true/false to 1/0 (True = 1, False = 0) =SUMPRODUCT($D$10:$D$249*({1,0,0,1,0,0,1,0,0,etc})) Then finally the sumproduct does what it does, and sums the result of the array of multiplications between each value in D10:D249 and the corresponding 1 or 0 in the array.

http://www.mrexcel.com/forum/excel-questions/621629-sum-every-nth-row.html