Summing non-consecutive cells

[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.

 

[Claus Busch]

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?

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


Regards
Claus B.

 

[Claus Busch]

Hi again,

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

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

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.

 

[Vidcapper]

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

Thanks for that - I guess I never think of SUMPRODUCT, probably because
I have no idea how it works.

 

[Vidcapper]

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

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

 

[Claus Busch]

Hi Paul,

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

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

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


Regards
Claus B.

 

[Vidcapper]

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

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?

 

[Claus Busch]

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)

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?

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.

 

[Vidcapper]

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.

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]

 

[Claus Busch]

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.

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



Regards
Claus B.

 

[Vidcapper]

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)

Thank you - that finally did the trick!