Count And Sum Excel Cells with multiple Colors (without VBA)

In this article, you will understand how in Excel you can count or sum a number of cells or row based on colors background without using VBA even If a data set has two or more colors.

The article is helpful only when a user color cells without using conditional format rule.

How many colors? Count Pink Balls

We will investigate three different ways to count color cells in Excel.

Download Your Example Excel File

  • While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.
  • Note: None of them is a perfect solution to count color cells in Excel, and each has its caveat.

Let’s start with the easiest and widely used option by using Excel’s Find and Replace feature.

Count or Add Colors using the Find option

In worksheet Find, you have a data set of Students and their Attendance where Absent is manually mark with Red background.

The assignment is to compute a number of cells with a red shade (Absent).

To Count Excel cells using the Find option

  • Go to worksheet ‘Find’ of Excel working file (Image instructions below)
  • Select range of cells C4:C23
  • Go to Home Tab and select Find & Select dropdown and Click Find… that open Find and Replace dialogue box (Shortcut- Ctrl+F)
  • Select the Format button
  • Click the Choose Format From Cell.. of Find Format dialogue box
  • Select the Red format cell using new plus sign with an eyedropper pointer
  • Click Find All button
Count color cells Excel Find feature
Count color cells with Find and Replace feature
Returns the count of color cell
Returns with cell count of Red shade

We can see that the number of red cells is four that means we have four absent students in the class. Although this is an Example with a small data set, we can easily replicate the same steps with large data sets. 

  • Try to replicate the query with a separate Workbook. The Excel solution file is just for reference.
  • These steps are simple but require manual interference every time to count color cells.
  • In case of multiple colors this method will be unmanageable at some point in time.

Count color cells using Table filter

The second method is dynamic and does not require same level of manual Input. We will be using the Excel filter along with SUBTOTAL function on an Excel Table. This method is suitable for counting more than two colors of a given data set.

In worksheet Table, you have a data set of Students and their Attendance. Attendance is marked with three categories ‘Present’, ‘Leave’ and ‘Absent’ with Green, Yellow and Red colors respectively.

The assignment is to return count cells with any of the selected color backgrounds.

To count color cell using Table Filter

  • Go to worksheet ‘Find’ of Excel working file (Image instructions below)
  • Select any cell of data set and convert it to Excel Table using Ctrl+T shortcut
  • Mark Total Row
  • Select Attendance filter Icon
  • Hover to Filter by Color and Select Yellow color
  • Subtotal function return count of 3 (only visible cell)
Convert list into a table using Shortcut 'Ctrl+T'
Convert list into a table using Shortcut 'Ctrl+T'
Select Total Row in new tabs after converting list into Table
Select 'Total Row' in new Design tab after converting list into Table
SUBTOTAL function count visible cells colors
Select the color format and SUBTOTAL function count visible cells

This is a useful function to count cells automatically by converting List into Table. Any addition or subtraction of row will return new cell count.
The caveat is that you must select drop down multiple times to see the number of counts for each color.

So, as above you learn how to use count cells with colors and now read with sum.

Same steps will be followed for sums numbers like in Attendance column instead of text (Absent, Present) there will be attendance in number i.e. total days come in one month then use filter to show results of student with least attendance. For this we will choose sum formula.

Returns sum of student with least attendance.
Returns sum of student with least attendance.

To see the number of counts of various colors at once, we will use Get statement.

Count multiple Color cell using Get statement

To create a robust way of counting color cell you will use Get. statement. Although it is a VBA Macro statement but does not require any coding per se. The GET.CELL function will extract the feature of a cell in this case type of color.

Simultaneously, we will use the Name Rage feature of Excel to create a formula to return the given color code.

In worksheet GET, all data points are the same as the previous example just the approach of counting the cell color is different.

The assignment is to count all three color cells at once.

To count cell with multiple colors

  • Go to worksheet ‘GET’ of Excel working file (Image instructions below)
  • Select Cell D5
  • Click Formula>Name Manager
  • Enter Name: ColorCode
  • Enter the formula in Refers to box: =GET.CELL(38,GET!C5)
  • Click OK
  • Enter new formula ‘ColorCode’ in cell D5
  • Now create =COUNTIF($D$5:$D$24,ColorCode) formula to count the number of color cells
Create your custom formula to generate color code
Create your custom formula to generate color code
Count color code using COUNTIF function
Count color code using COUNTIF function

Now you have an easy way of counting multiple cells with different colors. It reduces the time to count each cell once a user set up the Excel file correctly.

Cell D5 is a reference point of the formula do make necessary changes as required.

The disadvantage is that you have to refresh the formula each time after you make a change in the list.

  • You have to save Excel as a Macro-Enabled workbook
  • With a very large data set you may encounter Excel crash problem.
  • Not applicable for conditional format cell