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