Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF - counting across different spreadsheets
I'm starting a new thread, as I'm not sure if people have seen my reply in the old thread - please let me know if this is a problem! New day - new problem! My manager has now asked for a SUMIF summary to be on a separate spreadsheet (so that she can look at it along with other summaries) Unfortunately, using this formula: =SUMIF('S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127) just brings up a #Value! unless the spreadsheet it is linking to is open. If the linked to spreadsheet is opened, then closed, then I get the figures untill I close the summary spreadsheet. Then, when I reopen the summary spreadsheet, and click on Update (which I would need to if any of the other sections that this spreadsheet links to had been updated), I get #Value! again. I can't see why this doesn't work when the more simple formulae (eg a straight ='S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Summary'!$B$21) do. :( What am I doing wrong? -- JoFo ------------------------------------------------------------------------ JoFo's Profile: http://www.excelforum.com/member.php...o&userid=36481 View this thread: http://www.excelforum.com/showthread...hreadid=562786 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF - counting across different spreadsheets
=sumif() doesn't work with closed workbooks.
You could write it as =sum(if(...)) (and enter as an array formula) But you can't use the whole column. And I'm confused about what range you're checking with: $1:$65536 =sumproduct() can work with closed workbooks, too: =sumproduct(--(thatlongstring...!$a$3:$a$1001=1), (thatlongstring...!$b$1:$c$1001)) Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ======== Ps. Watch your ranges--they didn't match! $A$3:$A$1001 $C$3:$C$1127 Not sure which should be used. JoFo wrote: I'm starting a new thread, as I'm not sure if people have seen my reply in the old thread - please let me know if this is a problem! New day - new problem! My manager has now asked for a SUMIF summary to be on a separate spreadsheet (so that she can look at it along with other summaries) Unfortunately, using this formula: =SUMIF('S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127) just brings up a #Value! unless the spreadsheet it is linking to is open. If the linked to spreadsheet is opened, then closed, then I get the figures untill I close the summary spreadsheet. Then, when I reopen the summary spreadsheet, and click on Update (which I would need to if any of the other sections that this spreadsheet links to had been updated), I get #Value! again. I can't see why this doesn't work when the more simple formulae (eg a straight ='S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Summary'!$B$21) do. :( What am I doing wrong? -- JoFo ------------------------------------------------------------------------ JoFo's Profile: http://www.excelforum.com/member.php...o&userid=36481 View this thread: http://www.excelforum.com/showthread...hreadid=562786 -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF - counting across different spreadsheets
Thanks - I'll take a look at those links. (poor brain isn't working very well with this heat...) The ranges don't match because I did a scroll down when selecting them - we are not anticipating getting to 1000 visits or quotes over the year! I have a feeling that the only way I can do this (as I have to be able to look at the whole column) is to use the sumif within the individual workbooks, and then do an = across to the summary spreadsheet. -- JoFo ------------------------------------------------------------------------ JoFo's Profile: http://www.excelforum.com/member.php...o&userid=36481 View this thread: http://www.excelforum.com/showthread...hreadid=562786 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
More problems linking spreadsheets | Excel Discussion (Misc queries) | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
counting the number of printed spreadsheets | Excel Worksheet Functions |