Adding Multiple Pages(Tabs)

[Steven]

I currently use the following formula to add values together from one tab to
a totals page.

=SUMPRODUCT(--('1-500'!C4:'1-500'!C518="CY"),--('1-500'!H4:'1-500'!H518="Bus"),'1-500'!G4:'1-500'!G518)

But I have added another page (tab) which i need adding into the totals
pages as well. The same information is held on this new page and it is held
in the same column and rows. How do i adjust the above formula to include the
new page? The name page is called 501-1000.

Thanks

 

[Jacob Skaria]

Replace 1-500 with 501-1000

 

[Billy Liddel]

Stephen

Usually, the easiest way is to add two sheets one before 1-500! and call it
First, and the second after the second sheet and call it Last.

To sum the values in B5 in all sheets use =SUM(First:Last!B3)
to sum B5:B10 in all sheets use =SUM(First:Last!B5:B10)

Unfortunately, it seems you can't do that with SUMPRODUCT. I had to make the
Ranges absolute copy the formula and replace the Sheet references using Edit,
Replace then add the two formulas as below.

=SUMPRODUCT(--('1-500'!$C$4:'1-500'!$C$518="CY"),--('1-500'!$H$4:'1-500'!$H$518="Bus"),'1-500'!$G$4:'1-500'!$G$518)+SUMPRODUCT(--('501-1000'!$C$4:'501-1000'!$C$518="CY"),--('501-1000'!$H$4:'501-1000'!$H$518="Bus"),'501-1000'!$G$4:'501-1000'!$G$518)

HTH
Peter Atherton