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?
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)


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


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
Ken ButtersCommented:
When entering this formula...

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


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