| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF across multiple worksheets
I am collating a summary stock movement which using the sumif function i.e
SUMIF Sheet1!b$1:b$500,a5,a$1:a$500) Is there a way to shorten the formula as I have presently 40 sheets for one week which is causing me problems space wise (and hurts the eyes when looking at the formula) as the a5 is a rolling cell and there are 945 varying cells in the summary worsheet. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
On Mon, 11 Sep 2006 07:57:01 -0700, triffidbook
wrote: I am collating a summary stock movement which using the sumif function i.e SUMIF Sheet1!b$1:b$500,a5,a$1:a$500) Is there a way to shorten the formula as I have presently 40 sheets for one week which is causing me problems space wise (and hurts the eyes when looking at the formula) as the a5 is a rolling cell and there are 945 varying cells in the summary worsheet. It's not immediately clear what you're trying to achieve. The Sum If formula seems about as short as it could be and can't be shortened much more other than taking out the $s. Are you trying to sum data for one week ( and hence 40 sheets) What do you mean by A5 being a rolling cell? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
Yes I'm trying to sum multiple sheets that can contain similar information.
By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this clarifies it. So I've been using SUMIF (Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1:b $500,a5,sheet2!a$1:a$500).....etc "Richard Buttrey" wrote: On Mon, 11 Sep 2006 07:57:01 -0700, triffidbook wrote: I am collating a summary stock movement which using the sumif function i.e SUMIF Sheet1!b$1:b$500,a5,a$1:a$500) Is there a way to shorten the formula as I have presently 40 sheets for one week which is causing me problems space wise (and hurts the eyes when looking at the formula) as the a5 is a rolling cell and there are 945 varying cells in the summary worsheet. It's not immediately clear what you're trying to achieve. The Sum If formula seems about as short as it could be and can't be shortened much more other than taking out the $s. Are you trying to sum data for one week ( and hence 40 sheets) What do you mean by A5 being a rolling cell? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
Why not have the sumif at the top of each sheet, then just have the
summary sheet sum across the sheet range e.g. Sum(Sheet1:Sheet40!A5) Rgds On Mon, 11 Sep 2006 09:26:03 -0700, triffidbook wrote: Yes I'm trying to sum multiple sheets that can contain similar information. By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this clarifies it. So I've been using SUMIF (Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1: b$500,a5,sheet2!a$1:a$500).....etc "Richard Buttrey" wrote: On Mon, 11 Sep 2006 07:57:01 -0700, triffidbook wrote: I am collating a summary stock movement which using the sumif function i.e SUMIF Sheet1!b$1:b$500,a5,a$1:a$500) Is there a way to shorten the formula as I have presently 40 sheets for one week which is causing me problems space wise (and hurts the eyes when looking at the formula) as the a5 is a rolling cell and there are 945 varying cells in the summary worsheet. It's not immediately clear what you're trying to achieve. The Sum If formula seems about as short as it could be and can't be shortened much more other than taking out the $s. Are you trying to sum data for one week ( and hence 40 sheets) What do you mean by A5 being a rolling cell? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
The 'master' stock list is on the summary page and I am attempting to pick up
each seperate sheet which contains any of the master items then give me a total amount across all the sheets. To get by I have done the =sumif(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2 !b$1:b$500,a5,sheet2!a$1:a$500).....etc but for the 40 sheets the formula is too long to fit in. If you can advise on this it would make things much easier. Cheers "Richard Buttrey" wrote: Why not have the sumif at the top of each sheet, then just have the summary sheet sum across the sheet range e.g. Sum(Sheet1:Sheet40!A5) Rgds On Mon, 11 Sep 2006 09:26:03 -0700, triffidbook wrote: Yes I'm trying to sum multiple sheets that can contain similar information. By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this clarifies it. So I've been using SUMIF (Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1: b$500,a5,sheet2!a$1:a$500).....etc "Richard Buttrey" wrote: On Mon, 11 Sep 2006 07:57:01 -0700, triffidbook wrote: I am collating a summary stock movement which using the sumif function i.e SUMIF Sheet1!b$1:b$500,a5,a$1:a$500) Is there a way to shorten the formula as I have presently 40 sheets for one week which is causing me problems space wise (and hurts the eyes when looking at the formula) as the a5 is a rolling cell and there are 945 varying cells in the summary worsheet. It's not immediately clear what you're trying to achieve. The Sum If formula seems about as short as it could be and can't be shortened much more other than taking out the $s. Are you trying to sum data for one week ( and hence 40 sheets) What do you mean by A5 being a rolling cell? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
If the sheets are actually named Sheet1, Sheet2, Sheet3, etc, try...
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :40"))&"!B1:B500"),A5,I NDIRECT("Sheet"&ROW(INDIRECT("1:40"))&"!A1:A500")) ) If the sheets are named differently, list the sheet names in a range of cells, let's say D5:D44, then try... =SUMPRODUCT(SUMIF(INDIRECT("'"&$D$5:$D$44&"'!B1:B5 00"),A5,INDIRECT("'"&$D $5:$D$44&"'!A1:A500"))) If you download and install the free add-in Morefunc.xll, you can use... =SUMPRODUCT(--(THREED('Sheet1:Sheet40'!$B$1:$B$500)=A5),THREED(' Sheet1:Sh eet40'!$A$1:$A$500)) The add-in can be downloaded at the following link... http://xcell05.free.fr/ Hope this helps! In article , triffidbook wrote: The 'master' stock list is on the summary page and I am attempting to pick up each seperate sheet which contains any of the master items then give me a total amount across all the sheets. To get by I have done the =sumif(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2 !b$1:b$500,a5,sheet2!a$1:a$5 00).....etc but for the 40 sheets the formula is too long to fit in. If you can advise on this it would make things much easier. Cheers |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
I have tried this and it works fine on the individual sheets but I get a
#NAME report on the summary sheet! "Richard Buttrey" wrote: Why not have the sumif at the top of each sheet, then just have the summary sheet sum across the sheet range e.g. Sum(Sheet1:Sheet40!A5) Rgds On Mon, 11 Sep 2006 09:26:03 -0700, triffidbook wrote: Yes I'm trying to sum multiple sheets that can contain similar information. By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this clarifies it. So I've been using SUMIF (Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1: b$500,a5,sheet2!a$1:a$500).....etc "Richard Buttrey" wrote: On Mon, 11 Sep 2006 07:57:01 -0700, triffidbook wrote: I am collating a summary stock movement which using the sumif function i.e SUMIF Sheet1!b$1:b$500,a5,a$1:a$500) Is there a way to shorten the formula as I have presently 40 sheets for one week which is causing me problems space wise (and hurts the eyes when looking at the formula) as the a5 is a rolling cell and there are 945 varying cells in the summary worsheet. It's not immediately clear what you're trying to achieve. The Sum If formula seems about as short as it could be and can't be shortened much more other than taking out the $s. Are you trying to sum data for one week ( and hence 40 sheets) What do you mean by A5 being a rolling cell? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
Range("A5").Select
Range("A4:D1594").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A1:A2"), CopyToRange:=Range("F4:I28"), Unique:=False If I include LastRow = Cells(Rows.Count,"A").End(xlUp).Row it gives the same result as before. What am I doing wrong please? "triffidbook" wrote: I have tried this and it works fine on the individual sheets but I get a #NAME report on the summary sheet! "Richard Buttrey" wrote: Why not have the sumif at the top of each sheet, then just have the summary sheet sum across the sheet range e.g. Sum(Sheet1:Sheet40!A5) Rgds On Mon, 11 Sep 2006 09:26:03 -0700, triffidbook wrote: Yes I'm trying to sum multiple sheets that can contain similar information. By a rolling cell - Product abc is a5, product def is a6 etc ...I hope this clarifies it. So I've been using SUMIF (Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2!b$1: b$500,a5,sheet2!a$1:a$500).....etc "Richard Buttrey" wrote: On Mon, 11 Sep 2006 07:57:01 -0700, triffidbook wrote: I am collating a summary stock movement which using the sumif function i.e SUMIF Sheet1!b$1:b$500,a5,a$1:a$500) Is there a way to shorten the formula as I have presently 40 sheets for one week which is causing me problems space wise (and hurts the eyes when looking at the formula) as the a5 is a rolling cell and there are 945 varying cells in the summary worsheet. It's not immediately clear what you're trying to achieve. The Sum If formula seems about as short as it could be and can't be shortened much more other than taking out the $s. Are you trying to sum data for one week ( and hence 40 sheets) What do you mean by A5 being a rolling cell? Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
Hey Domenic,
I'm trying to do this same thing and used your sumproduct function with sheets named differently. But it's only returning the first item in the list (in your example range D5:D44). Any idea why this might be? Let me know, thanks. Chris On Wednesday, September 13, 2006 at 8:32:40 AM UTC-6, Domenic wrote: If the sheets are actually named Sheet1, Sheet2, Sheet3, etc, try... =SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1 :40"))&"!B1:B500"),A5,I NDIRECT("Sheet"&ROW(INDIRECT("1:40"))&"!A1:A500")) ) If the sheets are named differently, list the sheet names in a range of cells, let's say D5:D44, then try... =SUMPRODUCT(SUMIF(INDIRECT("'"&$D$5:$D$44&"'!B1:B5 00"),A5,INDIRECT("'"&$D $5:$D$44&"'!A1:A500"))) If you download and install the free add-in Morefunc.xll, you can use... =SUMPRODUCT(--(THREED('Sheet1:Sheet40'!$B$1:$B$500)=A5),THREED(' Sheet1:Sh eet40'!$A$1:$A$500)) The add-in can be downloaded at the following link... http://xcell05.free.fr/ Hope this helps! In article , triffidbook wrote: The 'master' stock list is on the summary page and I am attempting to pick up each seperate sheet which contains any of the master items then give me a total amount across all the sheets. To get by I have done the =sumif(Sheet1!b$1:b$500,a5,a$1:a$500)+sumif(sheet2 !b$1:b$500,a5,sheet2!a$1:a$5 00).....etc but for the 40 sheets the formula is too long to fit in. If you can advise on this it would make things much easier. Cheers |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF across multiple worksheets
chrismmatheson wrote:
Hey Domenic, I'm trying to do this same thing and used your sumproduct function with sheets named differently. But it's only returning the first item in the list (in your example range D5:D44). Any idea why this might be? Let me know, thanks. Chris Domenic wrote that post ***ELEVEN ****ING YEARS AGO!!!*** On Wednesday, September 13, 2006 at 8:32:40 AM UTC-6, Domenic wrote: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ It's *very* unlikely he's still paying attention. -- I got a new shadow. I had to get rid of the other one... It wasn't doing what I was doing. -- Steven Wright |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Functions across multiple worksheets | Excel Worksheet Functions | |||
| Sumif with multiple worksheets | Excel Worksheet Functions | |||
| Update multiple worksheets | Excel Discussion (Misc queries) | |||
| how do I arrange multiple worksheets from the same workbook | Excel Discussion (Misc queries) | |||
| Extracting data from multiple worksheets into a list | Excel Worksheet Functions | |||
Linear Mode
