trouble with #REF! error

[Mark Arthur]

This is my formula but if any cell in range c5-c41 is blank it returns a ref error

=IF(SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55")))=0,"",SUMPRODUCT(SUMIF(INDIRECT("'"&Settings!$C$5:$C$41&"'!H13:H55"),B3,INDIRECT("'"&Settings!$C$5:$C$41&"'!Y13:Y55"))))

 

[steve the fish]

For someone who is self confessed as being not too proficient at excel I really think you should be removing your blank cells rather than using the iferror. The iferror will mask other potential problems with your data other than the indirect pointing to a sheet with no name. It's up to you but has pitfalls.

 

[Mark Arthur]

If I could have it skip blanks then that would be ok. But like I said the blank spaces are a list that can be filled out on a settings page. The list of races could be as short as 5 or as long as 37. How do I have a dynamic list that can grow in other formulas as or if it’s filled in. I thought about using a table but once you delete the contents it remains the same size, so back to blanks the next time it’s filled in.