Sum across workbooks

  • Thread starter Thread starter Giri
  • Start date Start date
G

Giri

Hi,

Cell A1 in Sheet1 of Workbook1 is a numeric value. This value should be
the sum of all B1 of Sheet1 for all excel files in a
paricular path. Is there any way this can be done?

Example: Suppose i have 5 workbooks in C:\ path. Let Sheet1 --> B1 of
each workbook have value 6. Then cell A1 of workbook1 that resides in
another path should contain the value 30. If i remove one file from C:\
then A1 should show 24.

Note: I dont know if this is possible with a formula. A manual formula,
yes. But in my case the fomula has to be dynamic because the no. of
files in the path is not constant. But i am open to alternative methods
/ round-about methods as long as i can achieve the above like maybe i
have to maintain a list of files in workbook1 or the path or something
else. But, i would prefer not having a solution via vba code.

Appreciate your help in this regard. Thank You.

Regards,
Giri
 
Giri said:
Hi,

Cell A1 in Sheet1 of Workbook1 is a numeric value. This value should be
the sum of all B1 of Sheet1 for all excel files in a
paricular path. Is there any way this can be done?

Example: Suppose i have 5 workbooks in C:\ path. Let Sheet1 --> B1 of
each workbook have value 6. Then cell A1 of workbook1 that resides in
another path should contain the value 30. If i remove one file from C:\
then A1 should show 24.

Note: I dont know if this is possible with a formula. A manual formula,
yes. But in my case the fomula has to be dynamic because the no. of
files in the path is not constant. But i am open to alternative methods
/ round-about methods as long as i can achieve the above like maybe i
have to maintain a list of files in workbook1 or the path or something
else. But, i would prefer not having a solution via vba code.

Appreciate your help in this regard. Thank You.

Regards,
Giri
Click to expand...

One way, that may start off your thinking:
Put as many formulas as there could be workbooks in separate cells of your
Workbook 1 Sheet1, such as:
='C:\Temp\[Book1.xls]Sheet1'!$B$1
='C:\Temp\[Book2.xls]Sheet1'!$B$1
='C:\Temp\[Book3.xls]Sheet1'!$B$1
etc.
Let's say you have 10 of these formulas in A2:A11.
Where the workbooks exist, they will return numbers.
Any that refer to workbooks that don't exist will return #REF!.
You can then put a formula in A1 to sum those of cells A2:A11 that contain
numbers:
=SUM(IF(ISNUMBER(A2:A11),A2:A11))
Note that this is an array-formula, so has to be entered using
CTRL+SHIFT+ENTER rather than just ENTER.
 
Back
Top