0

A little question!

How can I sum up the values in the same cell of the all excel files, those are located in the same folder?

Thanks for your guidance!!!

=SUM('C:[A.xlsx]Sheet1'!A1, 'C:[B.xlsx]Sheet1'!A1. 'C:[C.xlsx]Sheet1'!A1, ...)

There are 35 files so I don't want to repeat it several times...

Also, Is there a way to prevent the cell from writing #REF and calculate missing cells (If the related excel file is missing) as Zero?

For example If I dont have the C.xlsx file, I want the cell to think of

C:[C.xlsx]Sheet1'!A1

as

0

in the formula. So there wont be #REF problem anymore...

I appreciate any other alternative routes too!!!

Thanks so much!

  • A perl script with the Spreadsheet::XLSX and Excel::Writer::XLSX CPAN modules would work nicely. – creidhne Aug 5 '16 at 22:51
  • @creidhne Sorry... Um... Is there a tutorial about using or installing them? – Fantasy Hero Aug 5 '16 at 22:56
  • Are you using Windows? Apple OS X? Are you familiar with perl? If you aren't, this might not be the right solution for you. – creidhne Aug 5 '16 at 23:41
  • Using a SUMIF, solution below, you can use this as a basis in CRITERIA in quotation marks. Note the "<>" is "not equal to", as apposed to "!" or "not". – ejbytes Aug 6 '16 at 1:39
1

Use a SUMIf formula: =SUMIF( range, criteria, sum-range )

range: cell(s) to evaluage
criteria: Your case - "Not Equal to ERROR"
sum-range: cells to add up


enter image description here

Note: If you use a single column (a table) to hold the value of each workbook, it's easier to manage and see where and which books are missing from the directory. Instead of sum(book1, book2...book50), sum the references in the table { e.g. let b2=[book1.xlsx]Sheet1$A$1 and b3=[book2.xlsx]Sheet1$A$1, then sumif(b1:b2, "<>#REF!", b1:b2) }. Further, you could "Name" the table (range of cells b1:b100) and the formula would reference like this: sumif(mytable, "<>#REF!", mytable).

  • Thanks a lot for your your great response!!!! Just a little question... Is there something to write as an address to represent upper folder??? – Fantasy Hero Aug 6 '16 at 14:22
  • @FantasyHero The first thing that comes to mind is "dot notation". I don't know if it works within Excel though. You can try it. A single dot "." means this folder, a dot-dot ".." means the parent (upper) folder. For example here is an address: C:/users/me/Desktop/images/funny. Say you are working in the directory "funny" and you want a photo from the images-folder. Without leaving the funny-directory you can call the photo by its name like this. String img = "../tree.jpg"; This is an example of regular instantiation in a programming environment.The tree.jpg resides in the image-directory. – ejbytes Aug 6 '16 at 23:23
  • Did the Answer given solve your original problem? If it did, just click the check-mark to accept answer. – ejbytes Aug 7 '16 at 1:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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