How to ignore #DIV/0! cells?

Posted on 2007-10-02
Last Modified: 2012-06-27

I'm working with Excel 2007 and I have a spreadsheet with data starting each row in column B and I want to sum it all in column A. In other words A1=Sum(B1:Z1), A2=Sum(B2:Z2) etc.

The problem is that the data is the result of calculations from other sheets and because of lack of info in those other sheets, sometimes the result is #DIV/0! (or other error). That makes A1=Sum(B1:Z1) also #DIV/0!.

My question is how to ignore those error cells? I want A1 to be the sum of B1:Z1 but to exclude the errors cells (or treat them as 0). However, I don't want to change the formulas in B1:Z1 (to use ISERR for example). Can it be done?

Another quick question: how to apply a formula to a range of cells without dragging with the mouse? For example if I want to apply the above A1=Sum(B1:Z1) to rows 2-1000 (with correct indexes of course), how can I do it without dragging the A1 cell down 1000 rows?

Thanks to anyone who may help

Question by:ajny
    LVL 33

    Accepted Solution

    perhaps something like this works out:
    LVL 14

    Expert Comment

    =IF(a7=0,"",sum(b7 .. z7)
    LVL 14

    Expert Comment

    =IF(a7=0,"",sum(b7:z7) , sorry should be :.
    I am not fully awake yet.
    LVL 9

    Expert Comment

    if you only deals with positive numbers and would like to avoid any error (#N/A, #REF, etc...) try this


    LVL 85

    Assisted Solution

    by:Rory Archibald
    To enter the formula in those rows all at once, click in the Name box to the left of the formula bar, type A1:A1000 and press Enter. Then type the formula but instead of pressing Enter at the end, press Ctrl+Enter together.

    Author Comment

    Thank you all for your quick help.

    Best regards.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now