Sum Across Different Sheet/ Simplifying a Formula

[vs77777]

Does anyone know how to simplify this formula? I am going to have more sheets that will have text names - not just sheet 1, sheet 2, sheet 3, etc. Ideally, it would be great if I could reference the entire group of sheet names and then be able to add more names to the group as I create more sheets. Thank!

=SUMPRODUCT(('Sheet 1'!$A$6:$A$1000=$A8)*('Sheet 1'!$E$5:$ZZ$5=D$5)*('Sheet 1'!$E$6:$ZZ$1000))+SUMPRODUCT(('Sheet 2'!$A$6:$A$1000=$A8)*('Sheet 2'!$E$5:$ZZ$5=D$5)*('Sheet 2'!$E$6:$ZZ$1000))+SUMPRODUCT(('sheet 3'!$A$6:$A$1000=$A8)*('sheet 3'!$E$5:$ZZ$5=D$5)*('sheet 3'!$E$6:$ZZ$1000))+SUMPRODUCT(('sheet 4'!$A$6:$A$1000=$A8)*('sheet 4'!$E$5:$ZZ$5=D$5)*('sheet 4'!$E$6:$ZZ$1000))

 

[Gerald Higgins]

Do you REALLY need to spread your source data across lots of different sheets ?

It's difficult to be certain without seeing your situation, but in many cases people THINK they need to have their data spread across multiple sheets (for example a different sheet for each day / month, or a different sheet for each branch of their organisation), but re-designing their application so that all the data is on a single sheet would bring many benefits, and this looks like the type of situation where this might apply.

 

[vs77777]

Unfortunately I do. Each sheet is a separate complex investment waterfall, with each having revenue, costs, net income, profit, on different rows and I need to sum data for different dates - with the dates across columns. However, the start date for each waterfall is different (so column C could be March 1st in sheet 1, 2016 and column C in sheet 2 could be June 1st, 2017). If there is no way to simplify this, I understand. Thank you for looking into it.

 

[Jonmo1]

It seems to me that this is a valuable piece of information on it's own
=SUMPRODUCT(('Sheet 1'!$A$6:$A$1000=$A8)*('Sheet 1'!$E$5:$ZZ$5=D$5)*('Sheet 1'!$E$6:$ZZ$1000))
As is this
=SUMPRODUCT(('Sheet 2'!$A$6:$A$1000=$A8)*('Sheet 2'!$E$5:$ZZ$5=D$5)*('Sheet 2'!$E$6:$ZZ$1000))
And so on for each sheet.

I can't imagine wanting to know the sum of all sheets, but NOT wanting to know the value of each individual sheet as well.

I would recommend designating 1 cell on each sheet (the same cell on all, say AAA1 for example)
So Sheet1 AAA1 =SUMPRODUCT(('Sheet 1'!$A$6:$A$1000=$A8)*('Sheet 1'!$E$5:$ZZ$5=D$5)*('Sheet 1'!$E$6:$ZZ$1000))
Sheet2 AAA1 =SUMPRODUCT(('Sheet 2'!$A$6:$A$1000=$A8)*('Sheet 2'!$E$5:$ZZ$5=D$5)*('Sheet 2'!$E$6:$ZZ$1000))
etc..

Then use
=SUM('Sheet1:Sheet10'!AAA1)

 

[vs77777]

Unfortunately that doesn't take into consideration different dates so aaa1 will correspond to a column with different date on sheet 1 than on sheet 2. So i'll be summing for numbers on the wrong dates.

 

[Jonmo1]

You would update the formula on each sheet to reference A8 and D5 on the sheet that contains the formula (call that summary for example)
And you wouldn't need the Sheet 1 references anymore either..

Sheet1 AAA1: =SUMPRODUCT(($A$6:$A$1000=Summary!$A8)*($E$5:$ZZ$5=Summary!D$5)*($E$6:$ZZ$1000))
Sheet2 AAA1: =SUMPRODUCT(($A$6:$A$1000=Summary!$A8)*($E$5:$ZZ$5=Summary!D$5)*($E$6:$ZZ$1000))