Forum Discussion

lilian51129's avatar
lilian51129
Copper Contributor
Oct 20, 2022
Solved

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. 

excel
Formulas and Functions

9 Replies

Sort By
    • lilian51129's avatar
      lilian51129
      Copper Contributor
      Oct 20, 2022

      Riny_van_Eekelen Just a question on the formula you suggested.

       

      I have the below column

       

       Idea 1
      Product Name0
      Case Size0
      Pilot Trials Required0
      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??

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Oct 20, 2022

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

Resources