give N/A value

[ApolloShine]

I am trying to add a formula that will give "n/a" in a cell a value based on info in header.

The formula i found in google forum is

=sum(arrayformula(if(C9:C33="N/A",value(mid(B9:B33,2,search(")",B9:B33)-2)),C9:C33)))

But results in error. Below is what my spreadsheet looks like.

https://docs.google.com/spreadsheets/d/1mPEt1lHTSsbvpIJCioantkJtQAjZL1JbPXeTLfjSDyc/edit?usp=sharing

This is the link to where i got the formula.

https://docs.google.com/spreadsheets/d/1mAQ2AvCQAJHF4EhRc5t8r33fVTJWl6TLmmAQcJNGf2o/edit?usp=sharing


Thanks for all the help in advance

 

[DRSteele]

I think your formula in C38 is mistakenly using column B in the VALUE and MID functions; it should be looking at A9:A33, not B9:B33. This kind of thing happens when one creates formulas with relative references instead of absolute references and then copies the formulas to other columns or rows.

 

[ApolloShine]

I think your formula in C38 is mistakenly using column B in the VALUE and MID functions; it should be looking at A9:A33, not B9:B33. This kind of thing happens when one creates formulas with relative references instead of absolute references and then copies the formulas to other columns or rows.

Oops that was a typo.

=SUM(arrayformula(IF(C9:C33="N/A",VALUE(MID(A9:A33,2,SEARCH(")",A9:A33)-2)),C9:C33)))

but when entered in excel 2010 it comes up as #NAME?

 

[DRSteele]

Well, your exemplar was not done in Excel, it was done in that googly thing. "Arrayformula" is not an Excel function. Which program do you want to use?

 

[ApolloShine]

AAHHH well that makes sense. I want to use Excel 2010..

Thanks again

 

[ApolloShine]

i figured it out!!!

i deleted arrayformula and use CSE formula thank you