| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Sum, several different sheets in the same workbook
Hello
I have tried below, and it works superb - but not all of the data i want to sumarise is on the same line on each sheet. so i want to do something like this =SUMIF(First:Last!A1:A202,A26,First:Last!B1:B202), but it is returning a #value. What am i missing? help, and thanks ----------------------------------------------------------------------------------------------------------------- Suppose your worksheets aren't in any kind of sequential order. Or you have 50 worksheets, and you only want to sum 30 of them, but you're constantly adding worksheets, so the beginning and end worksheet names may change? In this case, place a blank worksheet to the left of the first worksheet you want to include in the formula. Call that worksheet "First". Insert another blank worksheet after the last worksheet you want to include in the formula. Call that worksheet "Last". You can even hide these worksheets! Then, your formula will be: =SUM(First:Last!A2) -- Cheers "Anne Troy" wrote: Joe, maybe this will help: http://www.officearticles.com/excel/...rk sheets.htm If you have difficulty because totals are "at the bottom" but not in the same row, consider inserting a row at the very top, and using row 1 as your totals instead. ************ Anne Troy www.OfficeArticles.com "Joe" wrote in message ... I would like to summarise 20 sheets in the same workbook. I have a list of data in column A and then 6 lists of totals in columns B to G, on each sheet. I would like to add together the totals e.g. all the totals in column B together from each sheet, identified with the data in column A Although the data in column A is similar it is not identical and on the same line on each sheet. The summary sheet has all the data from column A. I started to use Vlookup putting the data into new columns on the summary sheet, and had 120 columns 6 from each sheet then just us a basic sum when all the data had fed into the summary sheet, but this proved to make the workbook really slow. has anybody got any other idea? Thanks -- Cheers |
|
#2
|
|||
|
|||
|
I can't make my sheet name include the ":"
try =SUMIF(FirstLast!A1:A202,A26,FirstLast!B1:B202) If this doesn't work, what is in B26? "Joe" wrote: Hello I have tried below, and it works superb - but not all of the data i want to sumarise is on the same line on each sheet. so i want to do something like this =SUMIF(First:Last!A1:A202,A26,First:Last!B1:B202), but it is returning a #value. What am i missing? help, and thanks ----------------------------------------------------------------------------------------------------------------- Suppose your worksheets aren't in any kind of sequential order. Or you have 50 worksheets, and you only want to sum 30 of them, but you're constantly adding worksheets, so the beginning and end worksheet names may change? In this case, place a blank worksheet to the left of the first worksheet you want to include in the formula. Call that worksheet "First". Insert another blank worksheet after the last worksheet you want to include in the formula. Call that worksheet "Last". You can even hide these worksheets! Then, your formula will be: =SUM(First:Last!A2) -- Cheers "Anne Troy" wrote: Joe, maybe this will help: http://www.officearticles.com/excel/...rk sheets.htm If you have difficulty because totals are "at the bottom" but not in the same row, consider inserting a row at the very top, and using row 1 as your totals instead. ************ Anne Troy www.OfficeArticles.com "Joe" wrote in message ... I would like to summarise 20 sheets in the same workbook. I have a list of data in column A and then 6 lists of totals in columns B to G, on each sheet. I would like to add together the totals e.g. all the totals in column B together from each sheet, identified with the data in column A Although the data in column A is similar it is not identical and on the same line on each sheet. The summary sheet has all the data from column A. I started to use Vlookup putting the data into new columns on the summary sheet, and had 120 columns 6 from each sheet then just us a basic sum when all the data had fed into the summary sheet, but this proved to make the workbook really slow. has anybody got any other idea? Thanks -- Cheers |
|
#3
|
|||
|
|||
|
First:Last! Refers to two seperate sheets, the first one and the last one of
the 20 that i want to pick data up from. -- Cheers "bj" wrote: I can't make my sheet name include the ":" try =SUMIF(FirstLast!A1:A202,A26,FirstLast!B1:B202) If this doesn't work, what is in B26? "Joe" wrote: Hello I have tried below, and it works superb - but not all of the data i want to sumarise is on the same line on each sheet. so i want to do something like this =SUMIF(First:Last!A1:A202,A26,First:Last!B1:B202), but it is returning a #value. What am i missing? help, and thanks ----------------------------------------------------------------------------------------------------------------- Suppose your worksheets aren't in any kind of sequential order. Or you have 50 worksheets, and you only want to sum 30 of them, but you're constantly adding worksheets, so the beginning and end worksheet names may change? In this case, place a blank worksheet to the left of the first worksheet you want to include in the formula. Call that worksheet "First". Insert another blank worksheet after the last worksheet you want to include in the formula. Call that worksheet "Last". You can even hide these worksheets! Then, your formula will be: =SUM(First:Last!A2) -- Cheers "Anne Troy" wrote: Joe, maybe this will help: http://www.officearticles.com/excel/...rk sheets.htm If you have difficulty because totals are "at the bottom" but not in the same row, consider inserting a row at the very top, and using row 1 as your totals instead. ************ Anne Troy www.OfficeArticles.com "Joe" wrote in message ... I would like to summarise 20 sheets in the same workbook. I have a list of data in column A and then 6 lists of totals in columns B to G, on each sheet. I would like to add together the totals e.g. all the totals in column B together from each sheet, identified with the data in column A Although the data in column A is similar it is not identical and on the same line on each sheet. The summary sheet has all the data from column A. I started to use Vlookup putting the data into new columns on the summary sheet, and had 120 columns 6 from each sheet then just us a basic sum when all the data had fed into the summary sheet, but this proved to make the workbook really slow. has anybody got any other idea? Thanks -- Cheers |
|
#4
|
|||
|
|||
|
Have a look at the following link...
http://www.mcgimpsey.com/excel/threedsumif.html Hope this helps! In article , Joe wrote: Hello I have tried below, and it works superb - but not all of the data i want to sumarise is on the same line on each sheet. so i want to do something like this =SUMIF(First:Last!A1:A202,A26,First:Last!B1:B202), but it is returning a #value. What am i missing? help, and thanks |
|
#5
|
|||
|
|||
|
If the data is not the same cell on each worksheet, how do you know where it
is, what is the rule? -- HTH Bob Phillips "Joe" wrote in message ... First:Last! Refers to two seperate sheets, the first one and the last one of the 20 that i want to pick data up from. -- Cheers "bj" wrote: I can't make my sheet name include the ":" try =SUMIF(FirstLast!A1:A202,A26,FirstLast!B1:B202) If this doesn't work, what is in B26? "Joe" wrote: Hello I have tried below, and it works superb - but not all of the data i want to sumarise is on the same line on each sheet. so i want to do something like this =SUMIF(First:Last!A1:A202,A26,First:Last!B1:B202), but it is returning a #value. What am i missing? help, and thanks -------------------------------------------------------------------------- --------------------------------------- Suppose your worksheets aren't in any kind of sequential order. Or you have 50 worksheets, and you only want to sum 30 of them, but you're constantly adding worksheets, so the beginning and end worksheet names may change? In this case, place a blank worksheet to the left of the first worksheet you want to include in the formula. Call that worksheet "First". Insert another blank worksheet after the last worksheet you want to include in the formula. Call that worksheet "Last". You can even hide these worksheets! Then, your formula will be: =SUM(First:Last!A2) -- Cheers "Anne Troy" wrote: Joe, maybe this will help: http://www.officearticles.com/excel/...rk sheets.htm If you have difficulty because totals are "at the bottom" but not in the same row, consider inserting a row at the very top, and using row 1 as your totals instead. ************ Anne Troy www.OfficeArticles.com "Joe" wrote in message ... I would like to summarise 20 sheets in the same workbook. I have a list of data in column A and then 6 lists of totals in columns B to G, on each sheet. I would like to add together the totals e.g. all the totals in column B together from each sheet, identified with the data in column A Although the data in column A is similar it is not identical and on the same line on each sheet. The summary sheet has all the data from column A. I started to use Vlookup putting the data into new columns on the summary sheet, and had 120 columns 6 from each sheet then just us a basic sum when all the data had fed into the summary sheet, but this proved to make the workbook really slow. has anybody got any other idea? Thanks -- Cheers |
|
#6
|
|||
|
|||
|
sorry, sumif does not work for 3d equations
Workarounds include setting a common point for a sumif equation on each sheet and using a 3d sum function on that cell Writing a macro to do the sumif setting a series of sumifs on one sheet and summing the series. "Joe" wrote: First:Last! Refers to two seperate sheets, the first one and the last one of the 20 that i want to pick data up from. -- Cheers "bj" wrote: I can't make my sheet name include the ":" try =SUMIF(FirstLast!A1:A202,A26,FirstLast!B1:B202) If this doesn't work, what is in B26? "Joe" wrote: Hello I have tried below, and it works superb - but not all of the data i want to sumarise is on the same line on each sheet. so i want to do something like this =SUMIF(First:Last!A1:A202,A26,First:Last!B1:B202), but it is returning a #value. What am i missing? help, and thanks ----------------------------------------------------------------------------------------------------------------- Suppose your worksheets aren't in any kind of sequential order. Or you have 50 worksheets, and you only want to sum 30 of them, but you're constantly adding worksheets, so the beginning and end worksheet names may change? In this case, place a blank worksheet to the left of the first worksheet you want to include in the formula. Call that worksheet "First". Insert another blank worksheet after the last worksheet you want to include in the formula. Call that worksheet "Last". You can even hide these worksheets! Then, your formula will be: =SUM(First:Last!A2) -- Cheers "Anne Troy" wrote: Joe, maybe this will help: http://www.officearticles.com/excel/...rk sheets.htm If you have difficulty because totals are "at the bottom" but not in the same row, consider inserting a row at the very top, and using row 1 as your totals instead. ************ Anne Troy www.OfficeArticles.com "Joe" wrote in message ... I would like to summarise 20 sheets in the same workbook. I have a list of data in column A and then 6 lists of totals in columns B to G, on each sheet. I would like to add together the totals e.g. all the totals in column B together from each sheet, identified with the data in column A Although the data in column A is similar it is not identical and on the same line on each sheet. The summary sheet has all the data from column A. I started to use Vlookup putting the data into new columns on the summary sheet, and had 120 columns 6 from each sheet then just us a basic sum when all the data had fed into the summary sheet, but this proved to make the workbook really slow. has anybody got any other idea? Thanks -- Cheers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding specific sheets within a workbook | Excel Discussion (Misc queries) | |||
| Macros for Protect/Unprotect all sheets in a workbook | Excel Discussion (Misc queries) | |||
| How to hyperlink from a workbook to sheets in another workbook? | Excel Worksheet Functions | |||
| Copy comments to several sheets in a workbook? | Excel Worksheet Functions | |||
| Linking sheets to a summary sheet in workbook | Excel Discussion (Misc queries) | |||
Linear Mode
