MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summing Every 3rd (or nth) value in a range


Posted by Dan on January 25, 2002 9:52 AM

How can you sum every 3rd value in a single column range that is over 500 rows long. Thanks.


Posted by Aladin Akyurek on January 25, 2002 10:31 AM

=SUMPRODUCT((A1:A9)*(MOD(ROW(A1:A9)-ROW(A1),3)=0))

where A1 houses the first entry. Adjust to suit.

Posted by Juan Pablo G. on January 25, 2002 12:31 PM

You know, it's funny. Yesterday i added this as a Tip of the day.

Juan Pablo G.

Posted by Aladin Akyurek on January 25, 2002 12:42 PM

Just saw the Tip by your redirection. There is a crucial difference between the two though.

Aladin

=========

Posted by Juan Pablo G. on January 25, 2002 12:47 PM

This,

MOD(ROW(A1:A9)-ROW(A1),

haven't tested, but isn't this the same as =1 at the end of MOD ?

Juan Pablo G.

Posted by Juan Pablo G. on January 25, 2002 1:06 PM

Just tested it, and i see what the Row(A1) does. To avoid conflicts of where the data is, right ? i mean, if it starts in A2, A3 or A4673, right?

Juan Pablo G.