CODEWITHSUNDEEP
excelexcel-formula
kiwi1342
kiwi1342

Reputation: 1389

Excel: How do I sum cell values every nth colum

I have the following table:

enter image description here

and I'd like to have the total for each player but values are every 3 columns.

As you can see from the picture on the bottom part I wrote what manually I should enter.

For player 1

=SUM(D3;G3;J3...)

Player 2

=SUM(D4;G4;J4...)

and so on. What formula should I use to calculate automatically every 3 columns? I know how the MOD works but on the net I found too many examples each one using different methods and none worked so far. Can anyone help me please or point me to the right direction to understand how it works to get this data since I'll be using this a lot (get value from cell every nth column).

thanks

Upvotes: 2

Views: 3108

Answers (3)

Victor Moraes
Victor Moraes

Reputation: 972

The explanation on how it works can be found here (I advise you to bookmark this site for future references. They have many other helpful formulas and functions).
Applying this formula to your reality should be something like this for Player 1 (Not tested. Adjust the ranges as required): 

=SUMPRODUCT(--(MOD(COLUMN(D3:Z3)-COLUMN(D3)+1,3)=0),D3:Z3)

Upvotes: 0

elmer007
elmer007

Reputation: 1445

It looks like you should just be using SUMIFS here:

=SUMIFS(3:3,$2:$2,"TOT")

This will sum every value on row 3 (Player 1) where the value in row 2 is "TOT" (every 3rd column). Put this in cell B18 and just copy down in your column B.

Or, in case you change your column labels, you can refer to cell D2 instead of typing "TOT" in the formula:

=SUMIFS(3:3,$2:$2,$D$2)

Upvotes: 4

Jordan
Jordan

Reputation: 4514

Try this, it will total all the cells that occur every 3 columns beginning at column D. Z3 can be increased to any column you require:

=SUMPRODUCT((D3:Z3)*(MOD(COLUMN(D3:Z3)-1,3)=0))

Upvotes: 2

Related Questions