Oct 20 2022 12:04 AM
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.
Oct 20 2022 12:48 AM - edited Oct 20 2022 12:49 AM
SolutionOct 20 2022 12:50 AM
Oct 20 2022 01:01 AM - edited Oct 20 2022 01:01 AM
@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??
Oct 20 2022 01:11 AM - edited Oct 20 2022 01:32 AM
@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")
Oct 20 2022 01:40 AM
@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 |
Oct 20 2022 02:00 AM
@lilian51129 See attached. Not convinced, though, that your schedule is designed in the most effective way.
Oct 20 2022 02:09 AM
Oct 20 2022 02:38 AM
@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.
Oct 20 2022 09:43 PM
Oct 20 2022 12:48 AM - edited Oct 20 2022 12:49 AM
Solution