Replacing #VALUE!

[lukerees83]

I'm using the following formula in one of my workbooks to sum across all sheets:

=SUM(MONDAY:SUNDAY!AA15)

If one of the AA15 cells in the 7 sheets Mon-Sun contains #VALUE! then the above formula will return #VALUE! irrespective of the other cells which contain actual numbers. So I want to sum up those cells that contain real values and have the formula disregard any cells containing #VALUE!

Any thoughts on what my new formula sohlud read?

Cheers.

 

[VoG]

Try

=SUM(IF(ISERROR(MONDAY:SUNDAY!AA15),0,MONDAY:SUNDAY!AA15))

confirmed with CTRL + Shift + Enter.

 

[lukerees83]

In the first week I tried this it has not worked properly. Over the seven days the values that the formula should be adding up are 2 x 0, 3 x #VALUE! and one cell that contains a 2, so the result should of course be 2.

The returns a value of 0 however.

 

[DonkeyOte]

You would be best served adjusting your precedent cells to handle the error and return an appropriate non-error value (eg 0 or Null String - latter better if you wish to Average etc)

If you wish to leave as is then below would be a one option:

=SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW($1:$7),"ddd")&"!AA15"),"<>#VALUE!"))
confirmed with Enter

The reference to AA15 can be made to be dynamic as necessary. Worth bearing in mind that the above is Volatile given use of INDIRECT.