Forum Discussion
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 creating this formula but I did not find a way to add more possibilities:
=SI(NB.SI(H4;"*A*");"1";SI(NB.SI(H4;"*B*");"2";""))
With the formula, if the cell contains A I get 1 or if it contains B I get 2.
In the sheet I have at least 6 different prospect types and would like, depending on what is in the chosen cell a specific number. For example: A, C, D I want to get 1, B and E I want to get 2, etc.
Thanks in advance for your help !
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
9 Replies
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)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 !
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
