Ronderbecke
Board Regular
- Joined
- Oct 4, 2017
- Messages
- 73
Hey There,
I am currently using a lot of sumproduct formulas in my sheet and was wondering if there is an easier way. Every time I add someone to the page (which takes forever between all the calculations, I have to go through everything and copy-paste and change the name so that the formula keeps getting bigger and bigger. Not sure if there is a way to 3d map this or at least make it so it doesn't become to big for the cell as we grow?
Example:
=SUMPRODUCT(--(MOD(ROW('Travis Jones'!D4:D)-Row('Travis Jones'!D4)+1,12)=1),('Travis Jones'!D4:D))+SUMPRODUCT(--(MOD(ROW('Anders Jacobson'!D4:D)-Row('Anders Jacobson'!D4)+1,12)=1),('Anders Jacobson'!D4:D))+SUMPRODUCT(--(MOD(ROW('Kevin Nordeen'!D4:D)-Row('Kevin Nordeen'!D4)+1,12)=1),('Kevin Nordeen'!D4:D))+SUMPRODUCT(--(MOD(ROW('Blair Townsend'!D4:D)-Row('Blair Townsend'!D4)+1,12)=1),('Blair Townsend'!D4:D))+SUMPRODUCT(--(MOD(ROW('Darren Carter'!D4:D)-Row('Darren Carter'!D4)+1,12)=1),('Darren Carter'!D4:D))+SUMPRODUCT(--(MOD(ROW('Tony Price'!D4:D)-Row('Tony Price'!D4)+1,12)=1),('Tony Price'!D4:D))+SUMPRODUCT(--(MOD(ROW('Dustin White'!D4:D)-Row('Dustin White'!D4)+1,12)=1),('Dustin White'!D4:D))+SUMPRODUCT(--(MOD(ROW('ER Magwood'!D4:D)-Row('ER Magwood'!D4)+1,12)=1),('ER Magwood'!D4:D))+SUMPRODUCT(--(MOD(ROW('Vincent Smith'!D4:D)-Row('Vincent Smith'!D4)+1,12)=1),('Vincent Smith'!D4:D))+SUMPRODUCT(--(MOD(ROW('Ron VonderBecke'!D4:D)-Row('Ron VonderBecke'!D4)+1,12)=1),('Ron VonderBecke'!D4:D))+SUMPRODUCT(--(MOD(ROW('Jonathan Elkin'!D4:D)-Row('Jonathan Elkin'!D4)+1,12)=1),('Jonathan Elkin'!D4:D))+SUMPRODUCT(--(MOD(ROW('Justin Parrish'!D4:D)-Row('Justin Parrish'!D4)+1,12)=1),('Justin Parrish'!D4:D))+SUMPRODUCT(--(MOD(ROW('Tim Schuyler'!D4:D)-Row('Tim Schuyler'!D4)+1,12)=1),('Tim Schuyler'!D4:D))
Thanks one of the easier formulas. Is there a better way to be doing this?
I am currently using a lot of sumproduct formulas in my sheet and was wondering if there is an easier way. Every time I add someone to the page (which takes forever between all the calculations, I have to go through everything and copy-paste and change the name so that the formula keeps getting bigger and bigger. Not sure if there is a way to 3d map this or at least make it so it doesn't become to big for the cell as we grow?
Example:
=SUMPRODUCT(--(MOD(ROW('Travis Jones'!D4:D)-Row('Travis Jones'!D4)+1,12)=1),('Travis Jones'!D4:D))+SUMPRODUCT(--(MOD(ROW('Anders Jacobson'!D4:D)-Row('Anders Jacobson'!D4)+1,12)=1),('Anders Jacobson'!D4:D))+SUMPRODUCT(--(MOD(ROW('Kevin Nordeen'!D4:D)-Row('Kevin Nordeen'!D4)+1,12)=1),('Kevin Nordeen'!D4:D))+SUMPRODUCT(--(MOD(ROW('Blair Townsend'!D4:D)-Row('Blair Townsend'!D4)+1,12)=1),('Blair Townsend'!D4:D))+SUMPRODUCT(--(MOD(ROW('Darren Carter'!D4:D)-Row('Darren Carter'!D4)+1,12)=1),('Darren Carter'!D4:D))+SUMPRODUCT(--(MOD(ROW('Tony Price'!D4:D)-Row('Tony Price'!D4)+1,12)=1),('Tony Price'!D4:D))+SUMPRODUCT(--(MOD(ROW('Dustin White'!D4:D)-Row('Dustin White'!D4)+1,12)=1),('Dustin White'!D4:D))+SUMPRODUCT(--(MOD(ROW('ER Magwood'!D4:D)-Row('ER Magwood'!D4)+1,12)=1),('ER Magwood'!D4:D))+SUMPRODUCT(--(MOD(ROW('Vincent Smith'!D4:D)-Row('Vincent Smith'!D4)+1,12)=1),('Vincent Smith'!D4:D))+SUMPRODUCT(--(MOD(ROW('Ron VonderBecke'!D4:D)-Row('Ron VonderBecke'!D4)+1,12)=1),('Ron VonderBecke'!D4:D))+SUMPRODUCT(--(MOD(ROW('Jonathan Elkin'!D4:D)-Row('Jonathan Elkin'!D4)+1,12)=1),('Jonathan Elkin'!D4:D))+SUMPRODUCT(--(MOD(ROW('Justin Parrish'!D4:D)-Row('Justin Parrish'!D4)+1,12)=1),('Justin Parrish'!D4:D))+SUMPRODUCT(--(MOD(ROW('Tim Schuyler'!D4:D)-Row('Tim Schuyler'!D4)+1,12)=1),('Tim Schuyler'!D4:D))
Thanks one of the easier formulas. Is there a better way to be doing this?