How to count a value in external files in Excel

HIROYUKI TAMURA used Ask the Experts™
Would you advise me how to pull a data from cells to fomula (red and blue square)?
What does the last green square do in formula?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger

Hi there! :)

Try replacing that part (red and blue boxes) of the code with this:

Open in new window


Thank you, Noah.
I'm trying to pull a data "=SUMPRODUCT(('F2[A1]Sheet1'!$B$1:$B$1000=B$2)*1)"
but I don't know how to set a formula

NoahHardware Tester and Debugger

Okay. How about this?


Open in new window

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Concatenating will get you nowhere.

What you are trying to do is one of the holes in the Excel object model. You can build a range reference using the INDIRECT function--but it won't work on a closed workbook. Nor would you want it to, because INDIRECT is volatile and getting data from closed workbooks is an expensive (time-consuming) operation.

I suggest writing a VBA sub to build your SUMPRODUCT formulas a text, place them in the cells that need the results, then replace their equals signs with equals signs to force them to evaluate. The sub can go down your list of file names and put each one into a formula, and run that formula at a time of your choosing.

Finally, the *1 in your SUMPRODUCT is there to coerce the Boolean expression inside the SUMPRODUCT into returning a 1 or 0 value for each row, rather than TRUE or FALSE. You can add up ones and zeros, whereas the sum of TRUE and FALSE is 0.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial