Summing every third cell

[bthieson]

I have quite a large excel sheet with about 210 columns. I need a sum at
the end of each row for every third cell. I have tried a couple
different formulas that should work, but they always end up adding
other values in for some reason. The cells I need added go like so:
E3,H3......HA3. I'm sure a few of you out there have dealt with this
before. If you have a resolution, I would definately appreciate your
response.

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(E3:HA3),3)=2),E3:HA3)

If you might ever insert new columns before the start of the range:

=SUMPRODUCT(--(MOD(COLUMN(E3:HA3)-COLUMN(E3),3)=0),E3:HA)

Biff

 

[Daniel CHEN]

Try some formula like this:
(array formula, ending with Ctrl+Shift+Enter)
=SUM(IF((MOD(ROW($A$1:$A$999),3)=0),$A$1:$A$999))


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================

 

[bthieson]

Okay I ended up using the first one and it worked perfectly. I have a
second totalling column on the end right next to the cell where I put
this formula. I assumed it would apply exactly the same, but it
definately does not.

I need to total every column cell starting at F3:HB3 also. I don't
understand why this wouldn't work exactly the same. If you have an
answer, I would definately appreciate it.

-Ben Thieson

 

[Biff]

I need to total every column cell starting at F3:HB3 also.

Every column? F3,G3,H3,I3,J3,K3,L3........HB3 ?

=SUM(F3:HB3)

The other formula you wanted only every 3rd column to be summed. If that's
also what you want with this new formula: sum every 3rd column from F3 to
HB3:

=SUMPRODUCT(--(MOD(COLUMN(F3:HB3),3)=0),F3:HB3)

I don't understand why this wouldn't work exactly the same.

The technique is the same but the condition is different because the range
is different.

The formula tests the column numbers to see if that particular column should
be included in the sum.

F3 = column() = 6
G3 = column() = 7
H3 = column() = 8
I3 = column() = 9

Using the MOD function we then need to find a divisor that returns a
specific value and this establishes a pattern that we can take advantage of.

MOD(COLUMN(F3),3) = 0
MOD(COLUMN(G3),3) = 1
MOD(COLUMN(H3),3) = 2
MOD(COLUMN(I3),3) = 0

So, we're telling the formula to sum those columns where the column number
returns a MOD of 0 when the divisor is 3.

Biff

 

[bthieson]

Okay it works, but now I need to understand why. In the first formula we
used, the mod() had to equal 2, and in the second it had to equal 0. Now
I had a formula that I was trying before and I was always using equal to
0, why would it be 2 in the case of the first formula? I assumed that
because I was looking for every third column starting at the point I
did, that would mean I would want to mod() 3 and search for a 0. If you
wouldn't mind explaining the 2 to me, I would definately appreciate it.
The other thing I didn't understand in the formula, was the (--)? What
does that do. In my initial formula, I was using an if statement where
you had the (--). What is it?

-Ben

 

[Biff]

I'm not sure I can explain it any better than I already have:

The formula tests the column numbers to see if that particular column should
be included in the sum.

F3 = column() = 6
G3 = column() = 7
H3 = column() = 8
I3 = column() = 9

Using the MOD function we then need to find a divisor that returns a
specific value and this establishes a pattern that we can take advantage of.

MOD(COLUMN(F3),3) = 0
MOD(COLUMN(G3),3) = 1
MOD(COLUMN(H3),3) = 2
MOD(COLUMN(I3),3) = 0

So, we're telling the formula to sum those columns where the column number
returns a MOD of 0 when the divisor is 3.

The cell interval really doesn't have anything to do with it. Every other
cell, every 3rd cell, every 10th cell. It's the actual range that you need
to know, specifically, the column (or row) numbers.

What you need to do is compare the MOD of the column (or row) numbers and
experiment with the divisor until you find a good pattern. Sometimes it's
not so easy!

The first formula started in column E (5) and the second formula started in
column F (6) so a MOD with the same divisor would be different.

About the "--", see:

http://mcgimpsey.com/excel/formulae/doubleneg.html

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Biff