# How to sum every N-number of cells every M-th row in Excel?

I want to sum 3 columns of every other row in Excel.

I know how to do the "sum every 3 columns" part:

``````=SUM(OFFSET(\$A40,0,3*(COLUMN(A40)-(COLUMN(\$A40)-1))-3,1,3))
``````

How do I apply this formula so it sums every other row, starting with A40 ?

So A40, A42, A43 etc.

Edit:

Basically, if you look at this picture, I am looking for formula, that would allow to sum every red cell in N3 and then be copy-pasted to sum every yellow cell in O3. • Do you want your formula to sum every other row? – yass Mar 23 '17 at 21:08
• sorry, looks like I didn't quite explain myself, I will update – stpn Mar 24 '17 at 1:35
• By looking at the example (picture),You may need to sum the yellows ones in N3 and the red ones to O3 – yass Mar 24 '17 at 9:10
• @stpn are you allowed to add an extra column? – Salman A Mar 24 '17 at 10:25
• Your N's and M's are incorrect. What you want is to sum every Row or every other row or Rows/2, but columns to X or 3 in your example. – ejbytes Mar 25 '17 at 0:46

## 2 Answers

You can use the following, if the column you need to sum are consecutive (A,B,C) In O3 write:

``````=SUM(IF(MOD(ROW(A3:A19),2)=1,A3:C19,""))
``````

Press Ctrl + Shift + Enter (Array Formula)

This formula divide the row number by 2 if the result is 1 (the remain ) `(Mod(Row(),2)=1)` it will add the value of the corresponding cell in the columns(A,B,C) to the sum if the remain is 0 it will return ""

In N3 you can just change the rferences:

``````=SUM(IF(MOD(ROW(D3:D19),2)=1,D3:F19,""))
``````

Change the references to correspond your real Data

Using Array Formulae:

Copy and paste the formula below. After you've pasted this, rather than just hitting enter, press `CTRL+SHIFT+ENTER`

`=SUM(A2:E7*(MOD(ROW(A2:E7),2)=0))` ## Note:

MOD or Modulus, is looking at every 2nd Row, so your yellow row must correspond to EVEN row numbers, regardless of what your region is.