Excel: How do I sum cell values every nth colum

2
votes

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

3
excelexcel-formula
Tell me more about MOD. What have you tried? What happened? - Daniel Springer
Here is a detailed explanation on the formula. Have you seen it? Is it what you are looking for? - Victor Moraes
I decided to choose @elmer007 answer because it's easier to replace ranges using the INDIRECT function when working on a separate sheet. I now have sorting issues but I'll post another question. thanks to all - kiwi1342

3 Answers

3
votes

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

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

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)