Excel spreadsheet for work formula

New Contributor
Hi everyone, I've just started a new job that requires zone closures when an agent is no longer available.

I'm looking for an equation that can give me a traffic light system or some signal as to when a zone is full. For example...
Working zones
Agent 1 A C D
Agent 2 B C
Agent 3 A D

So in this case. If I were to tick or enter a digit into the Agent 1 and 3 boxes, it would signal that zone A needs closing as there are no longer any agents avaliable for that area.

I hope that makes sense! I haven't worked with excel since school, and I'm sure I was taught something similar to this, but any help would be greatly appreciated!

Thanks everyone!
3 Replies
What do you mean by zone?
What is your plan?

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.
* Knowing the Excel version and operating system would also be an advantage.

Thank you for your understanding and patience

I know I don't know anything (Socrates)


As variant that could be conditional formatting rule with formula like =COUNTIFS(region, cell) >1

Hi there, thanks for the reply!

Here is an example of what I will be using(attached spreadsheet).

My work system works using a method of zones and agents that work in that zone. What my job will be, is to manually close workzones when an agent is no longer able to do bookings for that particular day(when they have a full day of work)

Along the top are workzones, areas where agents work, the left is the agent ID numbers. The ticks are the zones each agent works in. So if I were to look at the bookings screen and see agent 2 isn't able to take further bookings, I can look at the excel sheet and see only agent 2 covers Peterbrough, therefore it needs closing.

This gets more complicated and this is a simplified version, so for example s Lincoln has 3 agents that work there. So I want a system where I can input a X into the agents line when he is full, and for an equation to work out when that particular zone needs to be closed.

So for S Lincoln, if agent 1, 9 and 14 are inputed with an X, the system can work out that a zone is full and needs closing.

Hopefully that is explained well, it's a tough one to put down into words!