Link Safe

We can think of several solutions, though all are workarounds, and most involve using extra columns. Option one would be to set a filter on each of the colours in turn. To do this, select the data in a single column and then, from the Data tab click on the Filter icon. A drop-down arrow will appear in the top row of the data selected. From there you can choose Filter by colour, starting with Green, for example. Paste a G for green in each cell next to the filtered cells. Next, filter by Red and put an R into the cells next to the filtered cells. Repeat for Yellow, and you’ll get a count of the number of cells containing R, G and Y.

If you’re using a formula to work out the colours using conditional formatting, a better method is to use the same criteria to count the cells. So if you’ve used a criterion for setting a cell to be filled with Red as Cell contents<20, you could use COUNTIF to count the number of cells in the range that have a value less than

20, which will give the same value as counting those cells that are formatted Red. So if your data range is A2:A20, you’d have:

-COUNTIF(A2:A20,"<-20")

The formula for the middle check is a bit more complicated. As you’re running Excel 2007, you can use the new COUNTIFS function that accepts multiple conditions:

—COUNTIFS(A2:A20,">20",A2:A20"<—30")

The final formula would be:

COUNTIF(A2:A20,">30")