Forum Discussion
excel help needed
You can indent up to 7 IFs; you have 4 classes: profit!
IF(COUNTIF(column 1;client)>0;"active";IF(COUNTIF(column 2;client)>0;"onboarding";IF(COUNTIF(column 3;client)>0;"prospect";IF(COUNTIF(column 4;client)>0;"inactive";""))))
[remember to lock columns cells with $ in order to count all cells for the clients in the rows below]
It may exist a more elegant formula for this, I'm sure; but it should work anyway.
edit: looking the final result, I may suggest adding an IF condition in the beginning to check how many times the client is listed: if there is more than 1 client with the same name, it should alert you. If not, if it's 0 leave blank, else check in which column it is.
thats a good point, here is the issue. I should have clarified more as there are 4 classes of clients. It goes like this
Active Onboarding Prospect Inactive
You can indent up to 7 IFs; you have 4 classes: profit!
IF(COUNTIF(column 1;client)>0;"active";IF(COUNTIF(column 2;client)>0;"onboarding";IF(COUNTIF(column 3;client)>0;"prospect";IF(COUNTIF(column 4;client)>0;"inactive";""))))
[remember to lock columns cells with $ in order to count all cells for the clients in the rows below]
It may exist a more elegant formula for this, I'm sure; but it should work anyway.
edit: looking the final result, I may suggest adding an IF condition in the beginning to check how many times the client is listed: if there is more than 1 client with the same name, it should alert you. If not, if it's 0 leave blank, else check in which column it is.
Thank you! worked like a charm.
Adding a conditional formatting to the class cells that shows "DUPLICATE" should be enlighten any ""error"" in a much more visible way.