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.

 

[xlmaniac]

Dear Jonmo1,
I was trying to practise with your suggested formula on my below mentioned dataset.
I could sum the total for all the rows with PA(it is giving correct result of 2400) but the sum of all the rows with FT is yielding 0.
Pls let me know where am I going wrong in the 1st Formula?
Could you help in tweaking this pls?

Excel Workbook
	F	G	H
1	Month	Type	Value
2	January	FT	100
3	January	PA	150
4	February	FT	200
5	February	PA	250
6	March	FT	300
7	March	PA	350
8	April	FT	400
9	April	PA	450
10	May	FT	500
11	May	PA	550
12	June	FT	600
13	June	PA	650
14		FT-Total	0
15		PA-Total	2400
14

 

[Jonmo1]

TRY
=SUMIF(F2:F13,"FT",G2:G13)
and
=SUMIF(F2:F13,"AT",G2:G13)

 

[Aladin Akyurek]

Would be a good idea to robustify the MOD bit...

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

 

[Jonmo1]

That's better, thanks.

Where would we be without Aladin's robustness..

 

[iklak]

These formulas work excellently, but...

I have an issue in a column where there are some cells with text. I don't know how to avoid the errors of multiplying boolean values and text and the formula ends with #VALUE error. Any suggestions?

 

[Aladin Akyurek]

These formulas work excellently, but...

I have an issue in a column where there are some cells with text. I don't know how to avoid the errors of multiplying boolean values and text and the formula ends with #VALUE error. Any suggestions?

Would you post the formula?

 

[iklak]

Any of the =sumproduct(...) formulas have the same issue. If you can fix the last one =SUMPRODUCT($D$10:$D$249*(MOD(ROW($D$10:$D$249)-ROW($D$10),3)=0)) I'll be able to use it in my specific case. Thanks for the reply

 

[Aladin Akyurek]

Any of the =sumproduct(...) formulas have the same issue. If you can fix the last one =SUMPRODUCT($D$10:$D$249*(MOD(ROW($D$10:$D$249)-ROW($D$10),3)=0)) I'll be able to use it in my specific case. Thanks for the reply

Try...

=SUMPRODUCT($D$10:$D$249,--(MOD(ROW($D$10:$D$249)-ROW($D$10),3)=0)))

Explanation: The comma syntax ignores text values in the sum range, the star syntax does not.

 

[iklak]

Try...

=SUMPRODUCT($D$10:$D$249,--(MOD(ROW($D$10:$D$249)-ROW($D$10),3)=0)))

Explanation: The comma syntax ignores text values in the sum range, the star syntax does not.

This works excelent. Can you explain what does -- stands for? I've noticed it in other posts and formulas but I don't understand it. Thank you very vrey much

 

[Aladin Akyurek]

This works excelent. Can you explain what does -- stands for? I've noticed it in other posts and formulas but I don't understand it.

SumProduct works on numbers. When we have TRUE/FALSE evaluations (the MOD bit subjected to a equality test would yield such), we need to turn them into numbers. The -- bit makes TRUE's 1's and FALSE's 0's. We call -- a coercer; another is +0.

See also:
http://www.mrexcel.com/forum/excel-questions/202204-find-last-cell-data-formular.html
http://www.mrexcel.com/forum/excel-questions/70547-count-function.html#post337954

Thank you very vrey much

You are welcome.