Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to take data from two separate workbooks laid out like so:

picker  | lines
--------+------
zf      |    4
ag      |   22
jw      |    8
zf      |    7
cr      |   39
ag      |   10
ag      |   16
ag      |    1
cr      |    4
ag      |    2

... and count the amount of times initials show up and compile them in a completely different workbook. this is the formula I have so far :

=COUNTIF('\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$B$4:$B$200,"CR")+COUNTIF('\\Foodcomp\c\Dog Food Log\2013\December\[(df) December 3, 2013.xls]Dog Food'!$B$4:$B$300,"CR")

This works great as long as the original workbooks are open on my computer but if they are closed all I get is #VALUE! Ideally I'd like to also have something add up the numbers associated to the initials in the cells beside them too but one thing at a time.

share|improve this question

2 Answers 2

The COUNTIF/SUMIF family of functions don't work with closed workbooks, try SUMPRODUCT, e.g. for a single workbook to replicate your count

=SUMPRODUCT(('\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$B$4:$B$200="CR")+0)

and to get the sum for column C when B = "CR"

=SUMPRODUCT(('\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$B$4:$B$200="CR")+0,'\\Shipping\C\Agri-Pet Log\2013\November\December\[December 3, 2013.xls]Ag & Pet Shipping'!$C$4:$C$200)

share|improve this answer

If you want to aggregate information from multiple closed workbooks, one way is to make use of Microsoft PowerQuery. This is a Business Intelligence addin for Excel that is capable of querying, filtering and aggregating information from many sources including other workbooks.

Though it is a bit of a monster to get your head around, it is free and fully supported by Microsoft. You don't need to learn any coding to do most things.

It will certainly be able to do what you want.

The only thing to watch out for is other people opening the workbooks. This locks them and will cause the queries to fail. If this happens, you have to wait for the workbooks to be closed and then simply refresh the query.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.