sumif with multiple cells address

[serouja]

Hi All,
I have cells that may have #DIV/0!, #VALUE! or #N/A, for example the addresses are:
A2
B2
C3
D4
E2

how to run sumif or sum for these cells, please note that I don't want to run "if" or "iserror" for each cell.

thanks a lot.
Sergey

[Pete_UK]

Probably better to get rid of the errors in the first place by amending the formulae that produce them. In Excel 2007 you can do this:

=IFERROR(your_existing_formula,"")

or you can use 0 instead of the "".

Hope this helps.

Pete

[kvsrinivasamurthy]

assuming your range is D5:M80 you can use any of the below array formula.

=SUMPRODUCT(IFERROR(D5:M80,0))

or

=SUM(IFERROR(D5:M80,0))

[serouja]

thanks for both replies, but if possible without IFERROR for each cell or IFERROR in the SUM function, thanks again..

[icestationzbra]

if cells A1 through A6 have these values:

1
2
#DIV/0!
#N/A
#REF!
5

you could use the following CSE formula to get the SUM, under the stipulations presented by you so far:

Please Login or Register  to view this content.