Forum Discussion
Error Checking Formula
Up to now, your examples didn't have "One Desk Setup", so the formula didn't take that into account.
New one:
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",OR(C2="One Desk Setup",COUNTIFS($B:$B,$B2,$A:$A,"*None")>0),"Alone",TRUE,"Shared")
Hi Hans!
This spreadsheet tracks all types of space within an office complex. Seats for employees, as well as other spaces.
"Alone" represents a space occupied by an employee where he resides in the space by himself.
"No" Is an arbitrary term indicating that the subject Excel row represents a space other than a seat for an employee. Examples would be a janitor closet, hallway, lobby, etc.
In G2:
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",COUNTIFS($B:$B,$B2,$A:$A,"*None")=0,"Shared",TRUE,"Alone")
Please check carefully.
Interesting. I see what you did with the formula; making it search for the word, "Desk".
I added two rooms with a sole occupant.
Why doesn't it see rooms 200 and 201 as unique, when they also have the word "Desk" in column C?
Up to now, your examples didn't have "One Desk Setup", so the formula didn't take that into account.
New one:
=IFS(A2="*None","V",A2="*Common","Comm",A2="*Hotel","Hotel",ISERROR(FIND("Desk",C2)),"No",OR(C2="One Desk Setup",COUNTIFS($B:$B,$B2,$A:$A,"*None")>0),"Alone",TRUE,"Shared")
So, there will be instances where there are 6 seats in an office.
2 Vacant
4 Occupied
The 4 occupied seats should show as "Shared".
This formula marks the 4 occupied seats in this same office as "Alone".
Can you help?