This is, to say the least, anything but self-explanatory. One has no idea what you want.
If it's a simple problem to solve in your mind, that's because you know what the problem is--but you haven't succeeded in describing it to anyone else, other than by saying, "Wow, is this simple!"
"Color coded" cells are lovely. What exactly do the colors mean?
"Some kind of pivot view table" is unacceptably vague. One does not program computers by specifying unacceptably vague tasks, but, rather, exhaustive detail, and then some. I'll settle for just, "This is what I am trying to accomplish: yadda yadda yadda," never mind the exhaustive detail.
Thank you for your anticipated cooperation.
Not sure why the mention of "count" even appears.
ALL YOU REALLY WANT is to display your output color-coded instead of in boring visual form.
Of course, that's rather a complex task for Excel--Excel because it's PROGRAMMATIC and we have to write a detailed macro that actually achieves the work (the equivalent of the user [= you] walking through a detailed dialogue), although it's a trivial one for Access. Any particular reason we must use Excel?
You can do anything in a macro. A formula won't cut it. Not every version will have the same capabilities exposed through macros. Also, as you move from version to version, umpteen functions disappear. This makes Excel programming extremely dicey--strange for a company that claims that backward compatibility is always one of its principal goals.
Let's look at this in the morning, O.K.? It's getting late here on the Yankee Coast. You may be surprised at the volume of work required to do something as simple as turn something red or yellow. It ALMOST isn't worth it. (You[ll find a Web page on "copy formatting from Excel cell using formula based on condition," but it ain't so: it sets up the formatting beforehand using BUTTONS, and the "formula" does precious little.)