Sum ignore na

[Excel777]

Hi,

i have two these formulas

i want to sum and ignore na to get result without using if statement

=indirect(address(row(bu4),match(bu$2,$a$1:$bq$1,0)))
+indirect(address(row(bu4),match(bv$2,$a$1:$bq$1,0)))

thanks in advance for help

 

[barry houdini]

Do you mean a text value "na" or error value #N/A? If it's the former then try

=SUM(indirect(address(row(bu4),match(bu$2,$a$1:$bq$1,0))),indirect(address(row(bu4),match(bv$2,$a$1:$bq$1,0))))

 

[Excel777]

value #N/A

THIS PART OF THE FORMULA HAVE value #N/A

indirect(address(row(bu4),match(bv$2,$a$1:$bq$1,0)))

 

[barry houdini]

Which version of excel are you using? - in Excel 2007 you can use IFERROR to return 0 instead of an error, i.e.

=iferror(indirect(address(row(bu4),match(bv$2,$a$1:$bq$1,0))),0)

although I'd suggest that it might be easier to use INDEX rather than INDIRECT and ADDRESS, like this

=IFERROR(INDEX(4:4,MATCH(BV$2,$A$1:$BQ$1,0)),0)

 

[Excel777]

Yes many thanks barry i'd use iferror and try with index,

many thanks for your help,