Adding in Excel

[Guest]

I have a time sheet. One row has the total number of Regular hours worked in
7 days. The next row has the total number of Overtime hours worked in those 7
days, the next row regular hours, the next overtime hours, and so on for 5
rows down. At the bottom of the sheet I need to add only the Regular hours in
one cell and I need the total Overtime hours in another cell. So I need a
formula that will add every other row, or every other number in the same
column.

 

[Guest]

The easiest method would be

=A1+A3+A5+A7+A9
=A2+A4+A6+A8+A10

You can also use something like

=SUMPRODUCT(MOD(ROW(A1:A10),2),A1:A10)
=SUMPRODUCT(1-MOD(ROW(A1:A10),2,A1:A10)

This way you can just change the range values if you want to expand the
range without specifying the specific cells.

 

[Guest]

Hi Sloth. If it's not too much trouble, would you mind explaining how that
formula works. I'm afraid I'm struggling with the mod and sumproduct
functions. Thanks in advance.

 

[Guest]

SUMPRODUCT multiplies values from two arrays
MOD take the remainder of the division
ROW give the row of the cell

Broken Down...
ROW(A1:A10) -> {1,2,3,4,5,6,7,8,9,10} *NOTE:{} signifies an array
MOD(ROW(A1:A10),2) -> {1,0,1,0,1,0,1,0,1,0}
SUMPRODUCT(~~~) -> {1,0,1,0,1,0,1,0,1,0}*{A1,A2,A3,A4,A5,A6,A7,A8,A9,A10}
this becomes...
1*A1+0*A2+1*A3+0*A4+...

the second formula uses 1- and becomes
0*A1+1*A2+0*A3+1*A4+...

Hope this helps

 

[Guest]

Thank you very much Sloth. That helps alot. I'm going to have to experiment
with it to fully capture the concept. Thanks again.

 

[Dave Peterson]

Another option.

Insert a helper column (say column A).
Put R, OT, R, OT, R, OT, ... down that column.

Then you can use a formula like:

=sumproduct(--(a1:a20="R"),(b1:b20))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

What's nice about this formula is that if you insert a row, you don't break the
formula. (When you add those Vacation hours and Sick hours and ....)