How to count a value in external files in Excel

HIROYUKI TAMURA
HIROYUKI TAMURA used Ask the Experts™
on
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?

Snap5.png
Comment
Watch Question

Do more with

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

Commented:
Hi there! :)

Try replacing that part (red and blue boxes) of the code with this:
CONCATENATE($F3,$A3)

Open in new window

Author

Commented:
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

Snap390.png
NoahHardware Tester and Debugger

Commented:
Okay. How about this?

=SUMPRODUCT(CONCATENATE("(","'",F3,"[",A3,"]","sheet1'!$B$1:$B$1000=B$2)*1)"))

Open in new window

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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