Sum Every Nth Row

[AlbertS]

I have googled this and found the technical answer. My problem is modifying the canned text answe r(it seems to be exactly the same on each reply I've found).

I have D10:D249. I need to sum D10, D13, D16, D19 (every 3rd row) in cell D250.

HELP is always appreciated. If you can break down the answer in baby talk, so I can understand why it works, all the better!

Thanks in advance.

 

[Jonmo1]

Try

=SUMPRODUCT($D$10:$D$249*(MOD(ROW($D$10:$D$249),3)=1))

 

[AlbertS]

That worked excellently. Can you explain why?

The reason I ask, is I also need a formula in D251 that will count D11,14,17 etc.

 

[Jonmo1]

There's alot going on in that formula...so this will be a bit of a read..

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.

Clear as mud right?
Hope that helps.

 

[AlbertS]

Crystal clear... So I'm just going to ask for help every time I need one of these

Fortunately, I was able to modify them to work for the entire worksheet. Here's hoping I never have to figure out why it worked.

 

[Jeff Meyers]

jonmo1 - Your explanation / walk-through is much appreciated!

 

[Jonmo1]

Perhaps an illustration may help...

I have numbers in D10:D19
We want to sum every 3 (D10,D13,D16,D19)

I've put =ROW(D10) in F10 and filled down
I've put =MOD(F10,3) in G10 and Filled down

Now the SUMIF sums each cell in D10:D19 where the corresponding value in G10:G19 equals 1.


The sumproduct does the same thing, without the intermediate formulas in column F and G.

Hope this helps...

Excel Workbook
	D	E	F	G
5	SumIF	Sumproduct
6	22	22
7
8
9	Numbers		Row#	Mod
10	1		10	1
11	2		11	2
12	3		12	0
13	4		13	1
14	5		14	2
15	6		15	0
16	7		16	1
17	8		17	2
18	9		18	0
19	10		19	1
Sheet1

 

[AlbertS]

Excellent depiction. That makes a lot more sense.

Thank you again for the solution!

 

[xlmaniac]

Dear Jonmo1,
Your solution has always has attracted me and I have learnt a lot from your excel expertise in the past.
Thanks a lot for sharing such an incredible/mesmerising solution and amazing explanation.
Thanks

 

[Jonmo1]

Glad to help, thanks for all the feedback.