Sumproduct multiple pages easier way?

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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top