• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to sum same cell from mulitple workbooks and ignore NA errors.

T

tpheath

New Member
I have 13 different workbooks. I need to sum the sum the values from B54 on the same sheet in each workbook. Some of these workbooks will contain an #N/A error in the cell, others will contain a numeric value. It is not an option to reformat the errors, we do not want to get rid of them. I just need to ignore them.


I have tried multiple formulas, with no success. Any suggestions?
 
Using 13 lookup cells and 1 summation cell in your summary workbook:

Use 13 cells which simply do a direct reference to cell B54 in each of the 13 different workbooks. (I'll assume they are in cells A2:A14)


To sum those cells and ignore the #N/A, you could do:

=SUMIF(A2:A14,"<1E99")


This says to sum everything that is a number less than 1*(10^99)
 
Thanks! Is there any way to do this without having all of the reference cells? While I said that I need to add the same cell in multiple workbooks, I have to do this so many times, that it will take forever for me to do all of the lookups. I will also need to update these periodically, which will take to much time out of my day.
 
Here is the closest I have been able to get. This works, but I would like to still find a way to get around evaluating each cell for an error, and maybe evaluate them as a whole?


=SUM(IFERROR(D57,0),IFERROR(B58,0),IFERROR(R59,0),IFERROR(L60,0),IFERROR(N61,0))
 
Not really. Since you have to create an individual reference (one for each workbook), I'm not sure if you'll really be able to save that much time. You could, of course, try and write some type of macro to generate the lookup formulas. Either way, it sounds like you're trying to pull in hundreds of different cells from different workbooks...which will take some effort (in writing formulas).


Sorry
 
Hi tpheath,


You do not have to evaluate each cell for an error, use the SUMIF as advised by Luke or use the following.


=SUMIF(B2:B5,"<>#N/A")
 
You can also use the data > consolidate feature to simplify the process of looking up references in multiple workbooks.


See this image - http://chandoo.org/img/playground/data-consolidate-example.png
 
You must log in or register to reply here.
Back
Top