Counting every other row - DSUM? SUMPRODUCT?

I have a report where I need to create a calculation to sum every other cell in a column.  I was successful in getting results using the following formula =SUMPRODUCT((MOD(ROW($AM$11:$AM$500),2)=0)*($AM$11:$AM$500)) .  I also tried using =MOD(ROW(AM9),AS2)=0.

Using the first formula I was able to match the sum that I have by selecting the cells manually for all of the even numbered row in the range.  The second formula resulted in a 0 sum.   My problem is that I have numbers in the odd cells that I need to sum as well. Is it possible to use either of the formulas from above to get the results without having to manually select the cells?
jmac001Asked:
Who is Participating?
 
barry houdiniCommented:
This version sums every other row starting with the first row in the range (in your case that will be the odd rows)

=SUMPRODUCT((MOD(ROW($AM$11:$AM$500)-ROW($AM11),2)=0)*1,$AM$11:$AM$500)

change the 0 to a 1 and it sums all the others

=SUMPRODUCT((MOD(ROW($AM$11:$AM$500)-ROW($AM11),2)=1)*1,$AM$11:$AM$500)

Note: the extra ROW function makes the formula more robust in that it will still give the correct results even if you add or delete rows in the worksheet

regards, barry
0
 
Ken ButtersCommented:
When entering this formula...

Press : CTRL+SHIFT+ENTER.  This will enter the formula below as an array formula.

 =SUM(IF(MOD(ROW(AM11:AM500),2)=0,AM11:AM500,0))

see: http://support.microsoft.com/kb/136738
0
 
jmac001Author Commented:
Thanks Barry that fixed the problem.  I also had deleted rows and it throw the calcuation off so the report is now accurate with the added Row function.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.