ajny

asked on

# How to ignore #DIV/0! cells?

Hi,

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

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

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
=IF(a7=0,"",sum(b7 .. z7)

=IF(a7=0,"",sum(b7:z7) , sorry should be :.

I am not fully awake yet.

I am not fully awake yet.

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

=SUMIF(B1:Z1,">0")

=SUMIF(B1:Z1,">0")

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thank you all for your quick help.

Best regards.

Best regards.