Microsoft Excel is a massive software with floods of data in its cells. Sometimes, it is necessary to highlight the duplicate cells to have more clarity about your data. Coloring cells will classify your data and make it easy to segregate it. There are different ways to color cells, but we have brought you the easiest ones you can use to count cells by color. Below are the four Best Ways to Count Cells by Color like a pro, so stick until the end of this tutorial.
Let us explore four easy ways in Excel to count cells by color. Make sure to go through the end of this tutorial because each method is unique.
1. Use The Custom Function
Create a new file in Google Sheets or open an existing one.This data set is color-coded in terms of priority.
Click on the Extension tab and select App Tools from the drop-down menu. It will open a new tab containing the default code. It would appear as follows;
function myFunction()
{
}
Delete this default function so that you can create a custom function.
Use our defined function instead, and after inserting the function, click on Save Project.
Once you have saved the project, it's time to select the destination to display the total value of colored cells and use the custom function that you just saved. We will use the following formula:
=countColoredCells(RANGE, DESTINATION COLOR)Finally, press enter, and specific cells will be summed up based on color coding.
2. Using the Find and Select Command
Go to the Home tab in your Excel Sheet and choose Find and Select.
It will open the Find and Replace menu. Click the Format button in the new small window and select Format Cell.
When you click on the small arrow inside the Format button, it will reveal more options, including setting the format by selecting a cell.
Then click on Find All, and it will show you all the cells that match the formatting.
Press Ctrl + A to select all the cells, and then press the Close button to change the colour of all these cells.
Finally, select the range in the sheet before pressing the Find All button to limit the search to the selection.
3. Use a LAMBDA Function to Count Colored Cells
The LAMBDA Function is used for building custom functions via the name manager. It is a new function; you must have Microsoft 365 licensed version to use it.
Go to the Formulas tab and click Define Name to open the New Name menu.
The formula also uses GET.CELL function should look like this:
= LAMBDA ( cell, GET.CELL ( 38, cell ) )Name your formula and add it to the Refers to section.
It will create a new GETCOLORCODE function that you can use inside the workbook, taking one argument called cell and returning the cell’s fill colour code.
4. Use Office Scripts to Count Colored Cells
Office Scripts is the latest and most innovative method to automate tasks in Excel. Most importantly, it is only available online and works with the Enterprise plan.
First, you must set up two named cells to which the code will refer by selecting any cell and typing a name like ColorCount into the name box.
Then create a Color named range for the input of the colour to count.
It creates a named range that can be referred to as Code. It also means we can move the cell, and the code will refer to its new location.
Go to the Automate tab in Excel online and click on the New Script command.
In the Scrip editor, give the following code and click on Run. It will populate the ColorCount named range with the count of the colored cells found in the Order ID column.
FAQ
1. Can you use Countif with color sheets?
The Countif function in Excel does not have built-in functionality to count cells based on their colour in colour sheets. It primarily works with specific criteria such as text, numbers, or logical conditions. Using VBA or specialised add-ins is recommended for this purpose. Some of these
2. How do I sum coloured text in Excel?
You cannot directly sum coloured text using a built-in function. The functions, like SUM or SUMIF, do not consider cell formatting, including the color of the text. However, you can achieve this by utilizing VBA coding.
3. How to Sum Colored Cells in Excel Without VBA?
Without VBA, summing coloured cells can be challenging. There are a few ways, like using the SUMIF function, creating a table to sum values of coloured cells, using the Find & Select option, and incorporating GET.CELL method can help you in this regard.
Summary
There are different methods to count cells by color; every method is unique in functionality. The methods mentioned in this article are easy to implement and effective ways of summing up a column using its color coding. Use these methods and automate your spreadsheet while saving time and effort. You may need the latest version of MS Excel to use certain methods like the LAMBDA function.
Moreover, all these methods are applicable in WPS Office as well. WPS Office is free software that serves as the best alternative to MS Office. It has features like collaborative work nature, a PDF tool, and a tabbed interface. Download WPS Office today and enjoy its amazing features.
