Summing non-consecutive cells

  • Thread starter Thread starter Vidcapper
  • Start date Start date
V

Vidcapper

If I wanted to sum, say every 3rd cell in a list, is there a way of
doing this without manually adding each cell into a formula?

TIA.
 
Hi Paul,

Am Mon, 27 Jan 2014 11:18:32 +0000 schrieb Vidcapper:
If I wanted to sum, say every 3rd cell in a list, is there a way of
doing this without manually adding each cell into a formula?
Click to expand...

to sum A3, A6, A9 ... in the range A1:A20
try:
=SUMPRODUCT((MOD(ROW(A1:A20),3)=0)*A1:A20)


Regards
Claus B.
 
Hi again,

Am Mon, 27 Jan 2014 12:26:37 +0100 schrieb Claus Busch:
=SUMPRODUCT((MOD(ROW(A1:A20),3)=0)*A1:A20)
Click to expand...

or every 4. cell starting with A1: A1, A5, A9...

=SUM(IF(MOD(ROW(1:20),4)=1,A1:A20))
and enter the formula w ith CTRL+Shift+Enter


Regards
Claus B.
 
Hi again,

Am Mon, 27 Jan 2014 12:26:37 +0100 schrieb Claus Busch:


or every 4. cell starting with A1: A1, A5, A9...

=SUM(IF(MOD(ROW(1:20),4)=1,A1:A20))
and enter the formula w ith CTRL+Shift+Enter
Click to expand...
Thanks for that - I guess I never think of SUMPRODUCT, probably because
I have no idea how it works. :p
 
Hi again,

Am Mon, 27 Jan 2014 12:26:37 +0100 schrieb Claus Busch:


or every 4. cell starting with A1: A1, A5, A9...

=SUM(IF(MOD(ROW(1:20),4)=1,A1:A20))
and enter the formula w ith CTRL+Shift+Enter
Click to expand...

Sorry, couldn't get either of the above to produce the expected result.
 
Hi Paul,

Am Mon, 27 Jan 2014 17:22:21 +0000 schrieb Vidcapper:


please have a look here
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Sum"
The yellow cells are summed
Click to expand...
Thanks for that.

I notice the formula in B1 :

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

Has a double minus sign, which the the one you posted yesterday didn't :

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

Might that have been why it didn't work?

What does the '--' do, anyway?

*************

I'll see if I can get this new version to work.

Would it work for other functions like Max, Min, Average, etc?
 
Hi Paul,

Am Tue, 28 Jan 2014 07:56:52 +0000 schrieb Vidcapper:
=SUMPRODUCT(--(MOD(ROW(A1:A15),3)=0),A1:A15)

Has a double minus sign, which the the one you posted yesterday didn't :

=SUMPRODUCT((MOD(ROW(A1:A20),3)=0)*A1:A20)
Click to expand...

no, both version will work. The double minus you need if you seperate
the arguments with a comma to change TRUE to 1 and FALSE to 0 that
SUMPRODUCT can caluculate with it. If you multiply the arguments it is
not needed.
Would it work for other functions like Max, Min, Average, etc?
Click to expand...
For example it works with average with:
=AVERAGE(IF(MOD(ROW(A1:A15),3)=0,A1:A15))
The array formula must be entered with CTRL+Shift+Enter
With MAX and MIN it works analog


Regards
Claus B.
 
Hi Paul,

Am Tue, 28 Jan 2014 07:56:52 +0000 schrieb Vidcapper:


no, both version will work. The double minus you need if you seperate
the arguments with a comma to change TRUE to 1 and FALSE to 0 that
SUMPRODUCT can caluculate with it. If you multiply the arguments it is
not needed.
Click to expand...

Still no luck - all I did was change the cell references to the ones
appropriate for my spreadsheet.

=SUMPRODUCT(--(MOD(ROW(Q6:Q98),3)=0),Q6:Q98)

It's Q8, Q11, etc I'm trying to total.

[I'm using Excel 2010, if that matters]
 
Hi Paul,

Am Tue, 28 Jan 2014 10:13:06 +0000 schrieb Vidcapper:
=SUMPRODUCT(--(MOD(ROW(Q6:Q98),3)=0),Q6:Q98)

It's Q8, Q11, etc I'm trying to total.
Click to expand...

Row8: =8/3 Mod=2
Change your formula:
=SUMPRODUCT(--(MOD(ROW(Q8:Q98),3)=2),Q8:Q98)



Regards
Claus B.
 
Hi Paul,

Am Tue, 28 Jan 2014 10:13:06 +0000 schrieb Vidcapper:


Row8: =8/3 Mod=2
Change your formula:
=SUMPRODUCT(--(MOD(ROW(Q8:Q98),3)=2),Q8:Q98)
Click to expand...

Thank you - that finally did the trick! :)
 
Back
Top