Sumifs on Multiple workbooks

derrickhnj

New Member
Joined
Nov 17, 2009
Messages
16
I have a master spreadsheet where we keep all our tax adjustments (M-1's) that I am looking to automate using the sumifs formula.

Basically, we have 120 separate M-1 files on our local drive with summary tabs and I'm saying if this summary sheet has a certain tax adj. code and for a certain entity, sum this column. So I need the sumifs for multiple queries (Entity and Tax Adj. code) for the Master Spreadsheet. Problem is Sumifs gives a #Value when these 120 workbooks are closed.

Is there any way to work around this? I tried an internet search and saw something on the MSFT site about using Array's, tried it but gives the same #Value problem and it also fails to lookup the information on the left columns in my master sheet.

Please help me, my spreadsheet is awesome and automated if only sumifs worked on closed workbooks.

Thanks,
Derrick
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

DoubleD

New Member
Joined
Nov 18, 2009
Messages
1
I think you are going to be hosed if you want to use SUMIF. But, if you switched all of your formulas to =SUM((....)) and made this an array formula it would work on closed workbooks. Depending on how many formulas you have this may not be a trivial update. Another option would be to have a simple macro that identifies all of the workbooks that you are linking to and opens them, recalculates and closes them without saving. This would give you fresh values and could be automated pretty easily. At least you have options.:)
 

PKTEE

Board Regular
Joined
Jan 24, 2006
Messages
174
Sumif do not work extracting information from different workbook.

Use sumproduct to resolve your problem
 

derrickhnj

New Member
Joined
Nov 17, 2009
Messages
16
I think I have to try the Macro to open all workbooks approach. Arrays simply don't work for my application.

This is odd though, once I made my workbook macro enabled (extension is now .xlsm) the sumifs didn't go to #value, and even when I close the file then re-open it the #value's don't come up anymore, does this sound right that just by making my workbook macro enabled Sumifs will work on closed workbooks?

Thanks,
Derrick
 

PKTEE

Board Regular
Joined
Jan 24, 2006
Messages
174

ADVERTISEMENT

It shouldn't work.

the #value didn't show up because you didn't click the option button
on the security warning message as the link had been disabled.
 

derrickhnj

New Member
Joined
Nov 17, 2009
Messages
16
I wrote the macro to open and close all the workbooks, worked out to 94 workbooks when I was done. It works great, only thing is you have to switch to manual calculations once you run the macro so that the links don't try to update when you filter data.

I have one question though, some of the workbooks I don't have to click the "do you want to save before closing" (no) button, and some I have to click it while the macro is running. Is there anyway to write into my macro so that I don't have to do any clicks while the macro runs?

Here is what my code looks like, and this is basically repeated for 94 different path files:

Workbooks.Open Filename:= _
"J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\Dividend Elimination Entry.xlsx" _
, UpdateLinks:=0
ActiveWindow.Close

If you didn't have to click the No don't save before closing button this macro would be perfect and would probably run in less than 1 minute.

Thanks for the help guys.
Derrick
 

Salamander

Board Regular
Joined
Jul 30, 2009
Messages
64

ADVERTISEMENT

If you didn't have to click the No don't save before closing button this macro would be perfect and would probably run in less than 1 minute.

Hi Guys,

This will close without asking if you want to save changes:

Code:
ActiveWorkbook.Close savechanges:=False

:)

S.
 

derrickhnj

New Member
Joined
Nov 17, 2009
Messages
16
Hi Guys,

This will close without asking if you want to save changes:

Code:
ActiveWorkbook.Close savechanges:=False

:)

S.


AWESOME!! That does it, now the macro runs in less than 2 minutes (3 files are very large that cause the problem) but 2 minutes to update 94 files in one worksheet for our department to see saves so much time on the backend.

Thanks so much for your help.
 

derrickhnj

New Member
Joined
Nov 17, 2009
Messages
16
Would you guys know why the values for 5 of my M-1's (Files opened) are not coming in even though the Macro is the same for these 5 files. I thought maybe it was a memory lag, but it's these 5 everytime.

here is my formula I have for one of the 5 that is giving me a problem:

=SUMIFS('J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[P990502 Penalties.xlsx]M-1 Upload'!$K:$K,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[P990502 Penalties.xlsx]M-1 Upload'!$A:$A,$B1093,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[P990502 Penalties.xlsx]M-1 Upload'!$C:$C,$D1093)

Here is the formula for one that isn't giving me a problem:

=SUMIFS('J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[Dividend Elimination Entry.xlsx]M-1 Upload'!$K:$K,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[Dividend Elimination Entry.xlsx]M-1 Upload'!$A:$A,$B11,'J:\COM_TAX\FEDERAL INCOME TAX\1120\1_30_10\SchM-1\[Dividend Elimination Entry.xlsx]M-1 Upload'!$C:$C,$D11)

They are identical so I don't know why they aren't showing up once I run my macro that opens up the workbooks (and I do see them open and close).

Thanks if you have any idea.
Derrick
 

Watch MrExcel Video

Forum statistics

Threads
1,123,122
Messages
5,599,826
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top