Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

If a range contains text then return a single value

Copper Contributor

If a range contains text then return a single value

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.

9 Replies
best response confirmed by lilian51129 (Copper Contributor)
Solution

Re: If a range contains text then return a single value

@lilian51129 You could try something like this instead:

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

Re: If a range contains text then return a single value

worked like a charm, thank you!

Re: 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??

Re: If a range contains text then return a single value

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

Re: If a range contains text then return a single value

@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

Re: If a range contains text then return a single value

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

Re: If a range contains text then return a single value

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

Re: If a range contains text then return a single value

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

Re: If a range contains text then return a single value

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.
1 best response

Accepted Solutions
best response confirmed by lilian51129 (Copper Contributor)
Solution

Re: If a range contains text then return a single value

@lilian51129 You could try something like this instead:

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