Excel is the quintessential tool for carrying out powerful math operations; Its various functions are very practical in the academic, business or personal field. For this reason, this multifunctional program also offers an alternative that we want to talk to you about today: counting cells with the same color in Excel.
Count cells with the same color in Excel – Easy and fast
Thanks to the large repertoire of formulas in Excel, calculation operations in the accounting, personal or business fields are carried out in a timely manner and in record time.
There are functions that are sometimes little known, such as counting the number of colored cells in an Excel spreadsheet . Today you will know another of its secrets.
Sort cells by color to quantify them
Selecting a color by cell values allows data to be grouped and summed through a custom formula; only we must create it ourselves. This function is very practical, mainly in the business field; this in order to add values that contain some specific cells , and obtain accurate results.
Learn to count cells of the same background color step by step
The background color in each of the cells that you have in a table of quantifiable values is a reference that will help you to count its content. It is an easy and fast process, just pay attention to the following steps that you must follow carefully
- First, open the Excel document to edit or perform the operation; you can do it on your mobile if you want; or open a new document with a blank sheet to record the values to be handled.
- Select a color for each cell : Position yourself on the cells to be colored; then in the “Format” tab and choose the color cell by cell. To take the reference color, go to any cell; it could be in a blank space on the sheet.
- Now, choose a fill color for the cell or color the background, take into account that you must do it with the same color of the cells that you previously selected.
- Open the Visual Basic editor to create the formula. How? by pressing the ALT + F11 keys.
- Create the custom function. To do this, write the following formula: Function AddUsingColor (Cell withColorA Sumar as Range, RangeASumar as Range).
- Now create the custom function. To do this, write the following: Function AddUsingColor (Cell withColorA Sumar as Range, RangeASumar as Range)
If you later want to modify the values , write in the following line: every time there is a change in the sheet, it causes the formula to be recalculated. Then type: Application Volatile.
Activate the reference color
- Follow this model: I declare a variable to store the reference color ; In the following line write: Din ColorReferencia as Integer. Later write: I declare a variable to store the current cell of a range to be added. Then: DinCeldaEnRango as Range.
- To initialize the reference color in the following line, place: We initialize the variable that contains the reference color, with the value of the color of the cell passed as the first argument.
- Then: ColorReference = CellWithColorASadd.Color.ColorIndex For Each CellIn Range in RangeTo Add. Then write: If the color of the reference cell matches the color of the current cell, then I accumulate the value of the cell to add it.
Do a loop
Write this command so that the parameter remains for future occasions: Loop through each of the cells of the range to be added. Then put: If Color Reference = CellInRango.Interior.ColorIndex Then. Finally write: AddUsingColor = AddUsingColor + CellInRango.Value
Check the function
Finally, to check the function, return to the sheet by pressing F11, go to one of the cells and place the = sign. A list will be displayed; select AddUsingColor.
Locate yourself in the cell where you put the color , then the range where the colored cells are, which by the way you can do it with keyboard shortcuts. Thus, if you change the color later, Excel will add the cells that contain only that color.
Now, counting cells with the same color is a process that is not complicated; but as you could see, it requires a lot of caution. If you liked this post, share it; and don’t forget to leave us your comments and suggestions.