Forum Discussion
If a range contains text then return a single value
Riny_van_Eekelen Just a question on the formula you suggested.
I have the below column
| Idea 1 | |
| Product Name | 0 |
| Case Size | 0 |
| Pilot Trials Required | 0 |
| New comps? | =IF(COUNTA(G4:G12),"yes","no") |
Is there any function I can add in front of the one you suggested so the no doesn't appear when the product name is 0??
lilian51129 Sorry, don't understand. In your latest example the Product Name is equal to 0 (zero) and the result will be "yes". I.e. the result will not be "no".
Edit:
Or perhaps you meant to write that the result should be "no" if the Product Name is equal to 0 (zero). Then you could use this:
=IF(G4,IF(COUNTA(G5:G12),"yes","no"),"no")Riny_van_Eekelen Apologies, I should have phrased it better, please bear with me!
I have uploaded an example of what I am trying to do.
On the Overview tab you can see Idea 1 and Idea 2. Idea 1 has C5 filed in as on the Product Ideas tab C3 is filed. What I would ideally want is when I don't have anything written on the product name cell on the product ideas tab (example: C9 in product ideas tab which is empty) , I want in the overview tab the new comps? cell to be giving me a 0 (zero) value instead of no.
So on the excel I have attached I would want the overview to look like this:
Idea 1 Idea 2 Product Name New product 0 Case Size 0 0 Estimated Volumes 0 0 Target selling cost 0 0 Shel life requirements 0 0 New comps? yes 0 lilian51129 See attached. Not convinced, though, that your schedule is designed in the most effective way.
- Thank you very much for the help. When you say it's not designed in the most effective way, do you have any recommendations?