Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please Help ~ Lookup from Multiple Pages/Tabs Huge Woorkbook.. | Excel Worksheet Functions | |||
Adding Multiple Excel Spreadsheets To One Another From Saved HTML Pages | Excel Worksheet Functions | |||
Fomula when adding the same cell across multiple tabs | Excel Discussion (Misc queries) | |||
Tabs v.Pages | Excel Worksheet Functions | |||
adding row headings to multiple pages - how do i input the rows? | New Users to Excel |