Sum a row that also has REF error in it.

[alancauchi]

I'm trying to sum a row of cells that has formulas in these cells and in certain cells there are the results of this formula and in some no (still needs to be worked out and till now it's showing as a REF error).

Whan I'm summing all up to get the total it's telling me also a REF error. Can I get aroung this with another formula rather then the SUM formula, please?

Alan

[martindwilson]

its far easier to fix the ref errors!

[alancauchi]

I cannot, because the REF errors are not actually errors. they are cells with formulas, where these formulas gather data from other tabs in the same workbook and till their date comes, these tabs cannot be created. So until then they will have to remain REF cells.

[darkyam]

I agree with Martin, but the formula itself could be =SUM(IF(ISNUMBER(A1:A3),A1:A3)) confirmed with Control+Shift+Enter (not just Enter).

[martindwilson]

Or
=sumif(b1:b5,"<>#ref!")

[alancauchi]

that's good thanks and like that I don't need to confirm with the Ctrl+shift+Enter

I think that this is the final solution

thanks very much

[daddylonglegs]

I cannot, because the REF errors are not actually errors. they are cells with formulas, where these formulas gather data from other tabs in the same workbook and till their date comes, these tabs cannot be created. So until then they will have to remain REF cells.

With Excel 2007 it's quite easy to replace errors with blanks, i.e.

=IFERROR(formula,"")

where formula is the current formula (without the first = sign)

then you can use a regular SUM formula to sum the range....and the display is more aesthetically pleasing, I would suggest

[alancauchi]

Do you insert this infront of the actual formula or in any cell?

[daddylonglegs]

My suggestion would be to replace the existing formula with the IFERROR version, e.g. if you have this formula in a cell

=VLOOKUP(A2,Sheet1!B2:C10,2,0)

then you just "wrap" it in an IFERROR function, e.g.

=IFERROR(VLOOKUP(A2,Sheet1!B2:C10,2,0),"")

That formula will return the result of the VLOOKUP formula....or if the VLOOKUP returns any error it will return whatever you show at the end, I used "", which is a blank, you could use a zero......

[alancauchi]

Ok I can adjust it like that and problem will be olved better like that, thanks.

By the way, any chance that you know how to color(highlight) items in a drop-down list, by any chance

[daddylonglegs]

By the way, any chance that you know how to color(highlight) items in a drop-down list, by any chance

As it happens....I don't....but in any case you should always start a new thread for a new question.......thanks

[alancauchi]

ok thanks just the same

[shg]

alancauchi, please don't quote whole posts. It's just clutter.

Thanks.

[alancauchi]

ok sorry, didn't know that I was clustering

[Macdave_19]

You could also use a combination of custom functions like so:

Please Login or Register  to view this content.

then just use =SUMNUMS(A1:A100)

cheers

[alancauchi]

thanks very much, but why use all this VB code when just a formula did it all?