SUMIF function not calculating from closed worksheet

We recently upgraded from Microsoft Office 2000 to 2007. Prior to the upgrade 
each department  would access a spreadsheet that would load data from a 
common  lookup spreadsheet 
The loading process was performed as follows:
The  lookup file and each department file would be opened to update the link 
for that month and then saved and closed.  When the department head would 
open the department  file, a question would appear asking if they wanted to 
update the link, if they answered no the spreadsheet would open and display 
the calculated data. If they answered yes, the file would only show values 
and accounting would have to relink and save.  
Since the upgrade to Office 2007 the departments cannot see the data unless 
the lookup file is also opened at the same time on the same computer – they 
do not get the question regarding updating the link.
The formula being used is a “Sum IF” formula that calculates the data 
displayed in the department spreadsheet  based on information in the lookup 
spreadsheet.– because of the way the files are setup we cannot use a “V 
Lookup”.

-- 
Bill
0
Utf
3/3/2010 8:56:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1326 Views

Similar Articles

[PageSpeed] 4

Unfortunately, SUMIF doesn't work with closed workbooks.  However, 
SUMPRODUCT can be used instead.  Something like this...

=SUMPRODUCT(--('C:\Users\Domenic\Desktop\[Book2.xlsm]Sheet1'!$A$2:$A$10="Criteria"),'C:\Users\Domenic\Desktop\[Book2.xlsm]Sheet1'!$B$2:$B$10)

Note that if the "Criteria" is a numerical value, remove the quotes.

-- 
Domenic
Microsoft MVP - Excel
www.xl-central.com, "Your Quick Reference to Excel Solutions"

"Bill" <Bill@discussions.microsoft.com> wrote in message 
news:C8B9D632-166A-4C85-92FD-F6EB30A2E92C@microsoft.com...
> We recently upgraded from Microsoft Office 2000 to 2007. Prior to the 
> upgrade
> each department  would access a spreadsheet that would load data from a
> common  lookup spreadsheet
> The loading process was performed as follows:
> The  lookup file and each department file would be opened to update the 
> link
> for that month and then saved and closed.  When the department head would
> open the department  file, a question would appear asking if they wanted 
> to
> update the link, if they answered no the spreadsheet would open and 
> display
> the calculated data. If they answered yes, the file would only show values
> and accounting would have to relink and save.
> Since the upgrade to Office 2007 the departments cannot see the data 
> unless
> the lookup file is also opened at the same time on the same computer – 
> they
> do not get the question regarding updating the link.
> The formula being used is a “Sum IF” formula that calculates the data
> displayed in the department spreadsheet  based on information in the 
> lookup
> spreadsheet.– because of the way the files are setup we cannot use a “V
> Lookup”.
>
> -- 
> Bill 

0
Domenic
3/3/2010 9:17:36 PM
Reply:

Similar Artilces:

Function Help Please
Tuesday, April 14, 2009 That data above, once imported from a tab delimited file, is interpreted by Excel as General format. This is a couple of years worth of this data. I'd like to shorten the date while removing the day from the beginning. Highlighting the cell then manually changing the format to Date does nothing. I think my only option is to use a function that keeps all data after the first space. Thanks, AJ Select the column with those "dates" in it, then click Edit/Replace on Excel's menu bar... type these four characters (without ...

Persoanl folder, etc. not closed properly
Computer running XP and Outlook 2003. Started slowing down and often won't open Outlook. Opening safe mode I get two messages: The data file "personal Folders" was not closed properly... The data file "Archives" was not closed properly... ....The file is being checked for problems. It doesn't seem to find any problems. I've run AdAware in safe mode, and AVG Virus Scan doesn't find any viruses. I've run SpyBot and VClean. I've cleaned up the drive and check for defragmentation. I've looked at all the start-up files and found one -- Wi...

"Get-ClusterResource" cmdlet throws "not recognized as a cmdlet, function, operable program, or script file" error.
Hi, I am trying to use "Get-ClusterResource" cmdlet to fetch "File Share Witness" property of CCR cluster. But when I run this command no "Exchange Management Shell" I am getting "The term 'Get- ClusterResource' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again." error. Any idea why I am getting this error? Do I need to add any "PSSnapin" via Add-PSSnapin cmdlet ? If so which one it is? Basically I am not able to run any of the cluster related GET cmdlets on Exchange Managem...

calculate dates #2
I am trying to create an application to calculate a date that is 2 weeks from today but I never want the date to be a weekend or holiday. The results I want to run on someones desktop without them having to do anything. Any thoughts? Use =WORKDAY(TODAY(),14) WORKDAY is part of the Analysis Toolpak add-in, so you may to install that -- HTH RP (remove nothere from the email address if mailing direct) "kevin.raway@lifefitness.com" <kevinrawaylifefitnesscom@discussions.microsoft.com> wrote in message news:A790A02C-0A02-42F0-9684-719436798CD6@microsoft.com... > I a...

remove filter on a form when closing
Hi All, I have form "frmRecipes" that can be called from two different places: from a button on a form named "frmUserRecipes" and from a button on the main switchboard. From "frmUserRecipes" I use this code to take me to a specific record: DoCmd.OpenForm "frmRecipes", acNormal, , "FoodCode=Forms!frmUserRecipes!OLDRNUM" This works fine. If I use the button on the switchboard I get prompted for "Forms!frmUserRecipes!OLDRNUM" when "frmRecipes" opens. I need it to open on the first record in this instance - no filter. I...

Closing assigned Activities
Hi I'm testing the MSCRM. When I assing an activity (linked to a case), the destination user cannot close the activity. If I assign a phone call, I want that the user assigned be able to close this activity. This is the correct behaivor? Which permission should I grant to the user so he can do so. Thanks in advance. -- Carlos I think write permission on activities should be eniugh to close an activity. Does your user already have this permission, but still not able to close? Jagan Peri Microsoft Business Solutions CRM "Carlos" wrote: > Hi > > I'm testing...

What function to determine the second small integer from a list?
I get a list of integers, and would like to determine which one is the second small integers from the list. Does anyone know what function to perform the above task? Thank for any suggestion Eric =small(range,2) "Eric" wrote: > I get a list of integers, and would like to determine which one is the second > small integers from the list. Does anyone know what function to perform the > above task? > Thank for any suggestion > Eric ...

Page numbering worksheets
Is it possible to number the pages in a workbook if l have multiple worksheets? I would like to number each worksheet within the workbook but l need the numbers to flow on from the previous sheets so l don't have to manually change the number on each worksheet. thankyou "H" <Hayley.Slater@emap.com> wrote in message news:06f501c3505e$df329d50$a001280a@phx.gbl... > Is it possible to number the pages in a workbook if l have > multiple worksheets? I would like to number each > worksheet within the workbook but l need the numbers to > flow on from the previous...

Copy/paste Function
When I try to copy a cell or a worksheet from one worksheet to another, it only gives me the option to create a picture - I can no longer paste the cell or worksheet. This is Excel 2007 ...

Automatic transfer of data from one worksheet to another
I have two spread sheets, let's call them A and B. A could be considered the master work sheet. It has 15 columns A -O. The B worksheet has 13 columns, all of them which come from A. I have deleted Columns L and N in A when the data is transferred to worksheet B. I would like to know what the process is to set these up so that when I add or delete new information to or from A, it automatically transfers the same information to B, exclusive of columns L and N. Thanks very much Lewis Shaks How about a simple cell linking function? Look at column A in Worksheet B as an example. Lin...

lines not printing on second copy of my excel worksheet
I use Excel daily, but have never had the lines not print on a 2nd copy. Everything else prints, but the lines are printing only on the first copy, not 2nd, 3rd, etc. ...

Calculations #4
Hi, when I am calculating my totals, my sum is more than what is listed. How do I make the necessary changes to reflect the correct sum. Thanks, Jessica This might describe your problem: http://www.mcgimpsey.com/excel/pennyoff.html In article <1959a01c44d55$091c3920$a601280a@phx.gbl>, "Jessica" <anonymous@discussions.microsoft.com> wrote: > Hi, > > when I am calculating my totals, my sum is more than what > is listed. How do I make the necessary changes to reflect > the correct sum. > > Thanks, > > Jessica Thanks for such ...

Calculating Days From Dates and printing letters.
I wonder if anyone can help me? Calculating Day of Week I am trying to use Outlook as my contact management software in the entertainment business. I have several hundred potential venues that run on different days in the week. When I create a new contact I would like to be able to; Input a date the venue has booked. This could be in the past or the future. From the date I have input have outlook calculate which day of the week the venue runs and store this information. Later I want to be ale to pull up for example; all venues that run on a Wednesday in a specific county Printing letters...

Sales Tax Calculation
Hi, What function or calculation could I use in a spreadsheet to calculate the price of an item minus the local sales tax so that in the end the final price would be a whole number. I would like to enter the local tax rate in percent so I could figure out what the price for an item should be so in the end the final price is $5, $10, $15 etc. Using Excel 2000. Thanks, Linda If the tax rate is in cell B1 and the final (total) price is in B2, use this formula in cell B3 (or any other cell): =ROUND(B2/(1+B1),2) Roy "LMB" <RomulanQueen@10Forward.SSTNG> wrote in mes...

folders in favourites are removed when outlook is closed
I have recently upgraded my pc to windows 7 and have outlook 2003 installed with outlook connector. i have added the inboxes from the hotmail email accounts to the favourites pane, but when outlook is closed and then re-opended the inboxes are no longer there. How do i stop this from happening? This issue can either occur because of a corruption in your Navigation Pane or somehow Outlook is not properly shutting down and the settings are not saved upon closing Outlook. To reset the Navigation Pane use; Start-> type; outlook.exe /resetnavpane To troubleshoot Outlook sh...

Using a Yes/No field to trigger a calculation in another field
I am trying to use an IIF statement to have a filed calculate based on another field having either Yes or No in it. The fields are: TotalTuitions - all the tuitions added together MultiClassTrigger - Yes/No fiield MultiClassDisc - 10% discount to be calculated depending on Yes/No IIF statement - MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0) This will work the first time that Yes is selected in the Trigger field but then the value will stay when I change the Trigger field back to No. With No selected it should change the discount field back to zero. ...

SUMIF Question
Can you do a range such as M4:O10? My spreadsheet let me put this in but it is not giving me the correct value. I have a 1 in cell O4 and it is giving me a value of "0" for this formula. =SUMIF('June08 Summary'!$E$4:$E$10,"DM",'June08 Summary'!M4:O10) -- Deb No, sumif and countif will only sum/count M4:M10 in your case. If you want to sum all entries in M4:O10 where E4:E10 regardless if there are multiple entries on the same row you can use =SUMPRODUCT(('June08 Summary'!$E$4:$E$10="DM")*('June08 Summary'!M4:O10)) -- Reg...

Email a single Excel worksheet instead of the whole workbook.
I have an Excel workbook with several worksheets. I want to email just one of these worksheets to a friend. It would be great to be able to right-click the worksheet tab and find the option to send worksheet as an attachment. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the ...

Disabling F1 key's Windows Help Function
We'll I guess the title says it all :) Just curious if there is an easy way to disable F1 from always trying to bring up Window's help whenever it is hit within the program. I find it more than a little annoying because even in my program where (I thought) I was capturing all keystrokes for a keyDown event (I added UINT MyClass:OnGetDlgCode() { return DLGC_WANTARROWS|DLGC_WANTALLKEYS|DLGC_WANTCHARS; } Hmmm, so can I add something else to this line perhaps (going to check on this now) in order to catch ALL keystrokes (or at least add something so windows help function will be di...

Can Function results be calculated?
Hi all I have a sheet with two columns of figures. I show the running totals of each column in cells with basic SUM Functions, at the top of the sheet. I now want to subtract one total from the other. How is this done? As the Function is looking at cells that themselves hold Functions, I can't see how to do this, short of making a longer Function that adds the column contents again and then subtracts. So, in summary: I have two cells with Functions. These are: Cell F4 =SUM(F8:F45) and Cell G4 =SUM(G8:G45) I want to do this: =F4-G4 but it won't play. Any advice most appreciated....

Calculated field in pivot table #4
I'm running Excel 2002. I'm unable to create a calculated field in a pivot table. I right-clicked on a cell in the table but I don't get the "Insert Calculated Field" window on my drop-down list. Do I need an Add-On? Am I doing something wrong? Thanks for any help. On the PivotTable toolbar, choose PivotTable>Formulas>Calculated Field Dan wrote: > I'm running Excel 2002. I'm unable to create a calculated field in a pivot > table. I right-clicked on a cell in the table but I don't get the "Insert > Calculated Field" window on my ...

using if function..please help
i am trying to use the if function in the following manner. A B apple red apple red grapes purple grapes yellow star n/a if(B1:B5="n/a",0,countif(A1:A5, "star") I want to use this formula not only for 'star' but also for 'apple' and grapes' and many more such fruits which might have n/a later. But, everytime I use this formula it returns a 1..should it not return a 0 for when the value in B is n/a? Please help.. Thank you Are you trying to say that if any c...

Exchange 2003/ 12 Architecture and Functionality Questions
1. Can we configure intelligent message filter in Exchange 2003/ Ex12 to also act on outgoing mails? Our customer would also like to make sure no unsolicited mails originate from their organization. 2. Does the facility to configure the users’ voice messages (which automatically answer other people’s calls) fall under the PABX, or will it have to be done in Exchange 12? 3. Is it possible for two organizations to make use two Exchange 12 Edge servers, while having Exchange Mail Box servers so they can take advantage of the native encryption and signing of mails to and from the two...

Calculations
Hi, i want to know if it is possible to calculate numbers in different colours or numbers (in black) with different backgrounds. ie. i only want to calculate numbers in red? Any help will be appreciated. T, DB Deon, You need to use VBA functions to do this. See www.cpearson.com/excel/colors.htm for details and example code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Deon" <deon@lando.co.za> wrote in message news:bp853b$ls0$1@ctb-nnrp2.saix.net... > Hi, i want to know if it is possible to calculate numbers i...

"Difference From" custom calculation in Pivot tables
I have been unsuccessful in using the "Difference From" custom calculation in Pivot tables. When I use this custom calculation (I can successfully use the % of Total for example) I get #N/A. Also, I'm not sure what the Base Field and Base Item boxes represent. I notice in the Base Item box there is "Previous", "Next" and a series of numbers which I don't understand what they represent. If someone could straighten my out on how to use this feature or point me in the direction of some instructions that would take me thru this process I would rea...