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.
Is "Class" the active/inactive text?
In that case, you can just check IF there is the name of the client in the left column (then say active), else check if there is in the right one (and say inactive) or leave blank if there is no such valor in any column (if you don't know if active or not).
IF(COUNTIF(left column;client)>0;"active";IF(COUNTIF(right column;client)>0;"inactive";""))
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.
