Sumproduct accross multiple sheets

  • Thread starter Thread starter guilbj2
  • Start date Start date
G

guilbj2

=SUMPRODUCT('Deacon, Carrie'!B4:B35,'Deacon
Carrie'!C4:C35)+SUMPRODUCT('Deacon, Carrie'!B4:B35,'Deacon
Carrie'!D4:D35)+SUMPRODUCT('Deacon, Carrie'!E4:E35,'Deacon
Carrie'!F4:F35)

I'm using the formula above to calculate some stats for a call center.
On a master "group summary page", I need to get the sum of the result
of this formula from 20 worksheets. Other than duplicating the formul
and changing the names, is there a faster way to do this? The cel
references are identical for all, worksheets just the names change.

The only thing I can think of is "(formula using name 1) + (formul
using name 2)..." and so on for every sheet in the book. This will b
incredibly long and it seems to me there has to be a better way. An
ideas
 
I would copy this formula into the *same* cell on each of your 20 sheets,
say A1:

=SUMPRODUCT(((B4:B35)*(C4:C35))+((B4:B35)*(D4:D35))+((E4:E35)*(F4:F35)))

Then, insert a blank sheet before your first sheet and name it "Start" (no
quotes), and another blank sheet after the last sheet and name it "End" (no
quotes), and then enter this formula on the "Master" sheet:

=SUM(Start:End!A1)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Back
Top