Add every 7th Row

  • Thread starter Thread starter Annette
  • Start date Start date
A

Annette

How can I add every 7th row in a spreadsheet to a formula without selecting
and adding each cell. I have about 300 cells that need to be added and I'm
sure there is a method available for this.
 
Hi
if your data is in A1:A300 try the following formula
=SUMPRODUCT(--(MOD(ROW(A1:A300)-1,7)=0),A1:A300)
 
Annette,

The following array formula will sum every seventh row, starting
at row 1, in the range A1:A100. Change the range reference to
meet your needs.

=SUM(IF(MOD(A1:A100,7)=1,A1:A100,0))

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
=SUMPRODUCT(--(MOD(ROW($A$2:$A$300)-CELL("Row",$A$2)+0,7)=0),$A$2:$A$300))

which sums every 7th value starting from the first value in the range.
 
I have been reading this group for a while, and learned a great deal. I see on a regular basis, two dashes (minus signs?) as the lead characters of the sumproduct function.
Please, what do they do?
 
It coerces TRUE and FALSE into their numeric (Excel) equivalents: 1 and 0,
respectively.
Arithmetic operators also effect such coercion when an operand is a truth
value. A formula with SumProduct needs such coercion when it contains
conditional expressions...

1]

=SUMPRODUCT(--(Range1="X"),--(Range2="Y"))

2]

=SUMPRODUCT((Range1="X")+0,(Range2="Y")+0)

3]

=SUMPRODUCT((Range1="X")*(Range2="Y"))


[1] to [3] all compute the same thing. For a review of the coercion
techniques, see:

http://tinyurl.com/yw3d9

Philco said:
I have been reading this group for a while, and learned a great deal. I
Click to expand...
see on a regular basis, two dashes (minus signs?) as the lead characters of
the sumproduct function.
 
Philco said:
I have been reading this group for a while, and learned a great deal.
I see on a regular basis, two dashes (minus signs?) as the lead
characters of the sumproduct function. Please, what do they do?
Click to expand...

Think this is not described in online help. Learned it by reading this
group.
"--" converts boolean (false, true) to numeric (0, 1).
Try "=--FALSE" which is 0.
 
Thanks Frank, Chip, Aladin ... this sure saves bunches of time since the
formula can not all fit in one cell due to the length.
 
Back
Top