Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3548
  • Last Modified:

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

 
0
ajny
Asked:
ajny
2 Solutions
 
Jeroen RosinkCommented:
perhaps something like this works out:
=SUMIF(B1:Z1,"<>#DIV/0!")
0
 
Glenn_MooreCommented:
=IF(a7=0,"",sum(b7 .. z7)
0
 
Glenn_MooreCommented:
=IF(a7=0,"",sum(b7:z7) , sorry should be :.
I am not fully awake yet.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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

0
 
Rory ArchibaldCommented:
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.
Regards,
Rory
0
 
ajnyAuthor Commented:
Thank you all for your quick help.

Best regards.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now