SUM Function to skip over cells with text so as not to produce "#VALUE!"

[Kevman403]

I have a very simple formula that is adding up certain columns within a worksheet, =SUM(AY3+BF3+BM3+BT3+CP3+CW3+DD3+DK3+EG3+EN3+EU3+FB3+FX3+GE3+GL3+GS3)
The issue is, if any of these cells contain text (which is always "Inst. Unavailable") it returns a value of "#VALUE!" .. to explain more, Within the worksheet, all the cells have number values, but randomly one of them could be "Inst. Unavailable"

I've tried using commas instead, =SUM(AY3,BF3,BM3,BT3,CP3,CW3,DD3,DK3,EG3,EN3,EU3,FB3,FX3,GE3,GL3,GS3) but the formula only returns a value of "0" when using this.

I've tried using "N", for instance, =SUM(N(AY3)+N(BF3)+N(BM3)+N(BT3)+N(CP3)+N(CW3)+N(DD3)+N(DK3)+N(EG3)+N(EN3)+N(EU3)+N(FB3)+N(FX3)+N(GE3)+N(GL3)+N(GS3) but again, the formula only returns a value of "0" hen using this.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: SUM Function to skip over cells with text so as not to produce "#VALUE!"
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Joe4]

Please post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.