0

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.

enter image description here

  • 1
    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
1

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

0

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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.