Sumproduct multiple pages easier way?

[Ronderbecke]

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?

 

[Tom Urtis]

Wow, talk about a headache looking for a cell to happen -- what a formula.

Your instincts are probably correct, in that there is almost certainly an easier way to accomplish whatever you are trying to calculate. Which brings me to ask, can you explain in words what you are doing here - - looks like a vertical range starting in cell D4 that seems to be of interest with a growing and overflowing list of peoples' names for sheet tabs. Basically, what are you doing, why, and what is your expected result, considering how these sheets are (perhaps) similarly designed?

 

[Ronderbecke]

Hey Tom,

Sure, I have many of these calculations that simply calculate the size of the systems, or pricing, or other numerical values that are important to track. It goes every 12 cells down starting at D4 on this one and calculates what has been added into the form as new numbers are added daily. This is one of the simplest equations and all the rest get more intense and involved, so I figured if I can find an easier way on this I can push it to the rest of the equations. Thoughts?