Adding Multiple Pages(Tabs)
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 |
Adding Multiple Pages(Tabs)
Replace 1-500 with 501-1000
-- If this post helps click Yes --------------- Jacob Skaria "Steven" wrote: 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 |
Adding Multiple Pages(Tabs)
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 "Steven" wrote: 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 |
All times are GMT +1. The time now is 06:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com