If a range contains text then return a single value

[lilian51129]

Hi there,

I have the below column in a tab called "Product Ideas":

New ingredients or comps

oat syrup

almonds

stuff

I want to create a formula in a new cell where it will read the range of cells under New ingredients or comps and if there is text written it will give me a single value of "yes", no matter how many new ingrdients there are. And if the range is empty I want to be getting the value "no".

Is this possible? I tried 

=COUNTIF('Product Ideas'!G4:G12,"yes") but it just gives me 0 back. 

[#2 · Accepted solution]

@lilian51129 You could try something like this instead:

=IF(COUNTA(G4:G12),"yes","no")

[#3]

worked like a charm, thank you!

[#4]

@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??

[#5]

@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")

[#6]

@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

[#7]

@lilian51129 See attached. Not convinced, though, that your schedule is designed in the most effective way. 

[#8]

Thank you very much for the help. When you say it's not designed in the most effective way, do you have any recommendations?

[#9]

@lilian51129 Personally I prefer to capture data in a tabular format. The Product Ideas tab already looks like a summarized overview to me. Conditional formats, merged cells, borders and empty rows, spaces to insert pictures etc. All this makes it more difficult to create the kind of Overview you are asking for.  It also transposes the Product Ideas from below each other to beside each other. Doable, but more complicated.

With only few ideas, I guess it's OK, but I fear it will be difficult to maintain for greater numbers. Haven't really given it much thought how to approach it from scratch as I'm neither familiar with your business nor with the purpose of this overview.

[#10]

Thanks it makes sense. I work in product developent and the company does only one or two new products at a time so I have put five product ideas just in case, not too many. The overview is just a summary for the senior managent to scan through in a glance and see what we are wanting to develop.