Forum Discussion

Alexandre Kimmerlin's avatar
Alexandre Kimmerlin
Copper Contributor
Apr 18, 2018
Solved

Nested IF formula

Hello everyone,   I am currently working on a prospect excel sheet and I would like to create an IF formula which gives a certain number depend on what is in the cell.   For now, I only managed c...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 18, 2018

    In my file formula works

    All Directors	2	=SUM(COUNTIF(A10,{"*Engineer*","*Expert*"}),COUNTIF(A10,{"*Director*","*Manager*"})*2)
Our Managers	2	=SUM(COUNTIF(A11,{"*Engineer*","*Expert*"}),COUNTIF(A11,{"*Director*","*Manager*"})*2)

    did you check it?

     

    perhaps you could attach small sample with your formulas

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 18, 2018

Hi Alexandre,

 

That could be like

=SUM((H4={"A","C","D"})*1,(H4={"B","E"})*2)

I guess in your locale that is

=SOMME((H4={"A";"C";"D"})*1;(H4={"B";"E"})*2)

 

 

  • Alexandre Kimmerlin's avatar
    Alexandre Kimmerlin
    Copper Contributor
    Apr 18, 2018

    Hello Sergei and thanks for your answer.

     

    The thing is, in each of the cells I want to choose from, there is a specific word like "Director", "Engineer" or "Expert" out of other words. If the cell contains ""Engineer" or "Expert" I get 1 and "Director" I get 2 for example.

     

    With my formula it works for now but I cannot add more.

     

    Using yours, for some reason, only gives me 0.

     

    Thanks for your help !

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 18, 2018

      When something like this

      =SUM(COUNTIF(A1,{"*Engineer*","*Expert*"}),COUNTIF(A1,{"*Director*","*Manager*"})*2)

      Please see attached. I assumed you don't have words from different groups in one cell simultaneously 

       

      • Alexandre Kimmerlin's avatar
        Alexandre Kimmerlin
        Copper Contributor
        Apr 18, 2018

        Hello Sergei,

         

        The first part of the formula works but it does not manage to get the "Directors" nor "Managers". It only brings up 0.

         

        I have managed to try out the following formula:

        =SI(NB.SI(H163;"*Engineer*");"1";SI(NB.SI(H163;"*Director*");"3";"2"))

        In this case, "Engineer"=1, "Director"=3 and "Neither one of them"=2. 

         

        I do not know what I am doing wrong...

         

        thanks a lot though !

         

Resources