How to count coloured cells in Excel (with numbered steps)

A countable colour cell in Excel is a type of cell used to store and track data. This type of cell is useful for keeping track of numerical and other data that professionals use in their everyday working lives. Countable colour cells are also helpful for creating charts and graphs. In this article, we look at how to count coloured cells in Excel using the COUNTIF method, whilst also identifying alternative processes that professionals use and why they may choose to do so.

How to count coloured cells in Excel

Knowing how to count coloured cells in Excel is important for professionals working within a wide range of industries as they're often required to work with a spreadsheet containing a lot of data. They use coloured cells for tracking purposes, for example, which enables them to categorise information according to a certain colour. One practical use is to highlight the cells representing tasks completed in green whilst also using red to convey what else needs completing. To present information in this way, the COUNTIF function is used:

1. Select the cells you want to count

One of the easiest ways to count coloured cells is to use the COUNTIF function. In the generic form of the formula, rng is a range of cells and criteria is a number, cell reference or text string that determines which cells are going to be later counted. For example, to count all green cells in the range A1:A10, use this formula:

=COUNTIF(A1:A10,"green")

It's important to use this formula in particular as it's case-sensitive. Any variations may mean that the formula either doesn't work as initially expected or that it doesn't work at all.

2. Enter the COUNTIF function in a blank cell

The first step here is to enter =COUNTIF( in a blank cell as this is the opening function. The next step is to select the range of countable cells. To do this, click on the first cell in the range and then hold down the Shift key while you click on the last cell in the range. This is going to highlight all of the cells in between. Now, enter a comma (,) and then select the countable criteria. For example, if you want to count all of the cells that contain the word 'Team', you enter "Team". To finish, close the function by entering a ). Your function then looks like this:

=COUNTIF(A1:A10, "Team")

Now hit Enter to see the result. The number that appears is the number of cells in the range that meet the criteria that you previously specified. In this case, it would be the number of cells that contain the word 'Team'.

Related: 15 basic Excel formulas to learn (with examples)

3. Enter the range of cells you want to count

When you're counting coloured cells in Excel, it's important to know how to properly enter the right range of cells to avoid ending up with an inaccurate count. To properly enter the countable range of cells, select the ones you want to include and then press the 'F5' key on your keyboard. This then brings up the 'Go To' dialogue box. In the 'Reference' field, type in the range of cells you want to count (for example, A1:A10) and then click the 'OK' button. This tells Excel which cells you want to include in the count.

4. Enter the criteria for counting cells

To count the coloured cells in Excel using the COUNTIF method, enter the criteria for the cell colour in the COUNTIF formula. To enter the criteria for cell colour, use the hex code for the desired colour. Find the hex code for a colour by Googling 'hex code for (colour)' or by using a Colour Picker tool found online. Once you have the hex code for the colour you want to count, enter it into the COUNTIF formula like this:

COUNTIF(A1:A10, "FFFF00")

This counts all cells in the range A1:A10 that are yellow coloured, for example. Note that the colour criterion requires enclosing in quotation marks and that the quotation marks are then followed by an exact 6 digit hex code. If you don't do this, Excel returns an error.

5. Press enter

Next, press enter. When you do this, the number of cells that contain the criteria that you've specified appear in the cell where you previously entered the COUNTIF formula. You can also use wildcards when entering the criteria for a cell colour. For example, if you want to count all of the green coloured cells, regardless of the particular shade of green, use the following formula:

COUNTIF(A1:A10, "00")

This is helpful when working with a wide range of data from which you want an overall view of progress as it combines different information categories together.

Alternative ways of counting coloured cells in Excel

One of the most common ways to count coloured cells in Excel is to use the COUNTIF function although not everyone likes to use this method. To cater to this, there are other ways to count the coloured cells in Excel that can be just as effective. For example:

The Macro method

If you're looking for a quick and easy way to count the number of coloured cells in an Excel spreadsheet, the Macro method is the way to go. This method uses a simple macro to loop through all the cells in a range and count the number of coloured cells. Note that the Macro Recorder is only able to count cells with a solid colour. This means that it won't count the cells that are either shaded or have a pattern.

To use the Macro method, first select the range of cells you want to count. Then, open the Macro Recorder (press Alt+F8) and record a macro while you colour one cell in the range. Stop recording when you're done and then playback the macro. The Macro Recorder is then able to loop through all the cells in the range, counting the number of coloured cells.

Related: What are Excel alternatives? (11 best software to use)

The Pivot Table method

The Pivot Table method of counting coloured cells in Excel is a powerful way to quickly and easily count the number of cells coloured in a given range. This method is particularly useful when working with large data sets, as it can save a lot of time and effort when compared to counting cells manually.

To use this method, simply select the range of cells you want to count, then click the 'Pivot Table' button on the ribbon. In the resulting dialogue box, choose 'Count' as your summarisation method, then click 'OK'. The pivot table is then able to show you the number of cells coloured in each row or column of your selection.

Related: How to use data consolidation in Excel: a complete guide

The Index and Match functions

The Index and Match functions method of counting coloured cells in Excel is a great way to keep track of data when using multiple cell colours. It can also help you keep track of data over a long period of time. This quick and easy method allows you to index your cell colours so that you can match them up with the right number. This way, you can easily see which cells are in which colour and count them accordingly. You can also use this method to monitor any colour changes.

The SUMPRODUCT function

The SUMPRODUCT function is often used to count the number of cells coloured in a given range or to count the number of cells that have a specific colour. To use this function, simply select the range of cells that you want to count and then enter the following formula into a cell:

=SUMPRODUCT(--(A1:A10="pink"))

This formula then returns the number of cells coloured pink in the range A1:A10. You can also use this function to count the number of cells that have a specific colour. To do this, simply replace 'pink' with the colour you want to count. For example, to count the number of cells coloured blue in the range A1:A10, you would use the following formula:

=SUMPRODUCT(--(A1:A10="blue")).

Why use coloured cells in Excel?

When you're working with a large amount of data, it can be difficult to spot patterns or trends. Counting coloured cells can help you quickly identify these patterns so you can take action on them. Using countable coloured cells can also help you spot errors in your data. This can be helpful if you're working with a large dataset and you want to make sure everything is accurate.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.