Counting Based on Background Color in Excel

As of Excel 2019, there are no built-in formulas to count cells based on background color (also known as fill color or interior color). The following user-defined functions (UDF) provide the capability to count all cells in a range with a particular background color.

Why would anyone need to count cells based on background color? There are usually better values or criteria available in the data set itself to count, right? Since you’ve found yourself reading this post, you must have a specific reason. For me, counting background color was needed to create an error checking dashboard. On a rather complicated Excel workbook, there were a number of different error checks throughout the workbook that would trigger conditional formatting when specific error conditions were met. Instead of creating additional helper columns to flag the error conditions, the conditional formatting rules turned a cell’s background color red if any error conditions were met. The dashboard view then used the COUNT_BACKGROUND_COLOR function to count all of these flagged errors (the red cells) and summarize the errors on an easy to monitor dashboard so that the errors could be corrected.

The two user-defined functions demonstrated in this post are GET_BACKGROUND_COLOR and COUNT_BACKGROUND_COLOR.

Usage

GET_BACKGROUND_COLOR(reference)

The GET_BACKGROUND_COLOR function evaluates a single cell and returns it’s color index. The color index is a numeric value corresponding to a particular color recognized by Excel.

ArgumentRequiredDescription
referenceYesReference to a single cell.

COUNT_BACKGROUND_COLOR(color index, range1, [range2], …)

The COUNT_BACKGROUND_COLOR function counts all cells in a specified range where interior color is set to the supplied color index.

ArgumentRequiredDescription
color indexYesNumeric value corresponding to a color; use xlNone for no color.
range1YesReference to a single cell or range of cells to evaluate.
[range2, …]NoAdditional references to a single cell or range of cells to include in the evaluation.

Known Limitations

If a large, used range is supplied to the COUNT_BACKGROUND_COLOR function, then Excel may become unresponsive. The function counts cells by evaluating each cell individually for the supplied color index. As an example, passing whole column references in a large data set may require evaluating 1,048,576 cells for each whole column. If whole column references are supplied to the function but the worksheet used range is mostly empty, then the function will ignore the empty cells and perform as expected.

Creating the Functions

Step 1 – Open Visual Basic

From the Developer ribbon in Excel, click Visual Basic.

The Microsoft Visual Basic for Applications window opens.

Step 2 – Insert New Module

From the Insert menu, click Module.

Excel VBA – Insert New Module
Excel VBA – Insert New Module

Step 3 – Add Code

Add the below code to the newly created module.

Step 4 – Close Visual Basic

Close the Microsoft Visual Basic for Applications window.

Result

After adding red, yellow, or green fill colors to a few cells on an Excel worksheet, we can test the two new functions. In these examples, the colors are applied to cells in the E2:G16 range.

Example 1 – Red Background Color

The GET_BACKGROUND_COLOR function returns a value 3 which corresponds to the red fill color used on cell E3.

=GET_BACKGROUND_COLOR(E3)

The COUNT_BACKGROUND_COLOR function returns a value 13. Manually counting all red cells in the E2:G16 range confirms that there are 13 red filled cells.

=COUNT_BACKGROUND_COLOR(3,E2:G16)

Example 2 – Yellow Background Color

The GET_BACKGROUND_COLOR function returns a value 6 which corresponds to the yellow fill color used on cell E6.

=GET_BACKGROUND_COLOR(E6)

The COUNT_BACKGROUND_COLOR function returns a value 5. Manually counting all yellow cells in the E2:G16 range confirms that there are 5 yellow filled cells.

=COUNT_BACKGROUND_COLOR(6,E2:G16)

Example 3 – Green Background Color

The GET_BACKGROUND_COLOR function returns a value 43 which corresponds to the green fill color used on cell G6.

=GET_BACKGROUND_COLOR(G6)

The COUNT_BACKGROUND_COLOR function returns a value 6. Manually counting all green cells in the E2:G16 range confirms that there are 6 green filled cells.

=COUNT_BACKGROUND_COLOR(43,E2:G16)
Excel – Counts Based on Background Color
Excel – Counts Based on Background Color

Source Code

Option Explicit

Function GET_BACKGROUND_COLOR(rCell As Range) As Long
  If IsObject(rCell) Then
    If rCell.Cells.Count = 1 Then
      GET_BACKGROUND_COLOR = rCell.Interior.ColorIndex
      Exit Function
    End If
  End If

  GET_BACKGROUND_COLOR = CVErr(xlErrRef)
End Function

Function COUNT_BACKGROUND_COLOR(lBackgroundColor As Long, ParamArray data()) As Long
  Dim lParamArrayIterator As Long
  Dim vCellIterator As Variant
  Dim lCellCount As Long: lCellCount = 0

  For lParamArrayIterator = LBound(data) To UBound(data)
    If IsObject(data(lParamArrayIterator)) Then
      Set data(lParamArrayIterator) = Intersect(data(lParamArrayIterator), data(lParamArrayIterator).Parent.UsedRange)

      For Each vCellIterator In data(lParamArrayIterator)
        If vCellIterator.Interior.ColorIndex = lBackgroundColor Then
          lCellCount = lCellCount + 1
        End If
      Next vCellIterator
    End If
  Next lParamArrayIterator

  COUNT_BACKGROUND_COLOR = lCellCount
End Function

Concatenate Ranges, Arrays, and Values Using Excel VBA

If you have Office 2019 or a subscription to Microsoft 365, then you have access to a useful Excel function named TEXTJOIN. This function concatenates text across ranges, arrays, and individual values and combines them into a single cell value. This function eliminates a multi-step process that typically involves concatenating individual cells across a range, copying/pasting the results to a text editor, and then removing line breaks.

As an alternative for those who do not have access to TEXTJOIN, the following is a user-defined function (UDF) named CONCATENATE_RANGE that mimics most of the TEXTJOIN functionality with similar performance.

Usage

CONCATENATE_RANGE(delimiter, ignore_empty, row_priority, text1, [text2], …)

The CONCATENATE_RANGE function evaluates each of the supplied ranges, arrays, and values and concatenates (combines) the identified values together into a single string separated by the supplied delimiter.

ArgumentRequiredDescription
delimiterYesA text string used to separate each concatenated value. The delimiter may be an empty string, one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
ignore_emptyYesIf TRUE, the function will ignore any empty values.
row_priorityYesIf TRUE, the function will first concatenate each value in a row first (moving across columns); if FALSE, the function will first concatenate each value in a column (moving down rows).
text1YesValue to concatenate. Parameter may be a text string, a cell range, or an array. If a number is supplied, it will be treated as text.
[text2, …]NoAdditional values to concatenate. Parameter may be a text string, a cell range, or an array. If a number is supplied, it will be treated as text.

Known Limitations

If the result exceeds 32767 characters, including the delimiter, the function returns the #VALUE! error. This is due to the inherent cell length limitation in Excel.

The function also does not work on 3D ranges (ranges that span multiple worksheets).

Creating the Function

Step 1 – Open Visual Basic

From the Developer ribbon in Excel, click Visual Basic.

The Microsoft Visual Basic for Applications window opens.

Step 2 – Insert New Module

From the Insert menu, click Module.

Excel VBA – Insert Module
Excel VBA – Insert Module
Excel VBA – New Module
Excel VBA – New Module

Step 3 – Add Code

Add the below code to the newly created module.

Step 4 – Close Visual Basic

Close the Microsoft Visual Basic for Applications window.

Result

All results from the following examples are displayed in the screenshot below.

Example 1 – TEXTJOIN

This example is used as the baseline to compare with the result from CONCATENATE_RANGE on the same data set.

=TEXTJOIN(", ",TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 2 – CONCATENATE_RANGE (row priority)

This example demonstrates that the result from CONCATENATE_RANGE matches the result from TEXTJOIN on the same data set.

=CONCATENATE_RANGE(", ", TRUE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 3 – CONCATENATE_RANGE (column priority)

When compared with Example 2, column priority sequences the resulting string by each entry in a column first.

=CONCATENATE_RANGE(", ", TRUE, FALSE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 4 – CONCATENATE_RANGE (row priority, include empty)

Similar result as Example 2 except empty values are included in the result.

=CONCATENATE_RANGE(", ", FALSE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 5 – CONCATENATE_RANGE (row priority, no delimiter)

Similar result as Example 2 except no delimiter is used to separate values.

=CONCATENATE_RANGE("", TRUE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)
Excel – CONCATENATE_RANGE Examples
Excel – CONCATENATE_RANGE Examples

Source Code

Option Explicit

Function CONCATENATE_RANGE(delimiter As String, ignore_empty As Boolean, row_priority As Boolean, ParamArray data()) As String
  Dim arrayData() As Variant
  Dim lParamArrayIterator As Long
  Dim vDataArrayIterator As Variant
  Dim sResult As String: sResult = ""

  For lParamArrayIterator = LBound(data) To UBound(data)
    If IsObject(data(lParamArrayIterator)) Or IsArray(data(lParamArrayIterator)) Then
      If IsObject(data(lParamArrayIterator)) Then
        If data(lParamArrayIterator).Cells.Count = 1 Then
          ReDim arrayData(1 To 1, 1 To 1)
          arrayData(1, 1) = data(lParamArrayIterator).Value2
        Else
          arrayData = data(lParamArrayIterator).Value2
        End If
      Else
        arrayData = data(lParamArrayIterator)
      End If

      If row_priority Then
        arrayData = Application.WorksheetFunction.Transpose(arrayData)
      End If

      For Each vDataArrayIterator In arrayData
        If ((ignore_empty And Not IsEmpty(vDataArrayIterator)) Or (Not ignore_empty)) Then
          sResult = sResult & vDataArrayIterator & delimiter
        End If

        If Len(sResult) > 32767 Then
          CONCATENATE_RANGE = CVErr(xlErrValue)
        End If
      Next vDataArrayIterator
    Else
      If ((ignore_empty And Not IsEmpty(data(lParamArrayIterator))) Or (Not ignore_empty)) Then
        sResult = sResult & data(lParamArrayIterator) & delimiter
      End If

      If Len(sResult) > 32767 Then
        CONCATENATE_RANGE = CVErr(xlErrValue)
      End If
    End If
  Next lParamArrayIterator

  If (Len(sResult) >= Len(delimiter)) Then
    sResult = Left(sResult, Len(sResult) - Len(delimiter))
  End If

  CONCATENATE_RANGE = sResult
End Function

Excel Lookup Using Date Ranges and Multiple Criteria in Power Query

Some data analyses are best solved with a proper database and a simple SQL query. In my opinion, this is one of those examples. However, such conveniences are not always available.

In this scenario, we have a table containing the number of hours each person worked on a particular date. In a second table, we have the hourly rate that is valid for a particular period of time for each person. We want to calculate the total cost for each person on each date using the hours worked and the valid effective rate for that particular date.

While this example focuses on hourly rates, the concept is applicable to countless scenarios such as looking up prices for time-based sales or discounts, employee salaries, document revisions, or data that has a defined lifespan.

In the expected result, we find the rate varies based on the person and the date to calculate the correct amount.

Excel – Expected Result with Hours, Rate, and Amount
Excel – Expected Result with Hours, Rate, and Amount

The Data

As I described earlier, we have two data sets in Excel. The first data set includes the hours worked on a particular date by a particular person. I’ll refer to the first data set as the Hours table. The second data set includes the valid rate for a particular person over an effective period defined by a start and end date. I’ll refer to the second data set as the Rate table.

Excel – Hours Table
Excel – Hours Table
Excel – Rate Table
Excel – Rate Table

Method 1: Excel Array Formula

Before we get into the Power Query solution, I’ll provide a solution using an array formula (or CSE formula). This is certainly easier than adding the overhead and complexity needed for Power Query. However, I find the formula to be more difficult to read and maintain for the average Excel user. I described a similar solution in an earlier post titled Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula. If you decide to use this formula, make sure you press Ctrl + Shift + Enter to confirm the formula.

This formula assumes the Hours data set is on a worksheet named HOURS and the Rates data set is on a worksheet named RATES.

=INDEX(RATES!$D$2:$D$5,MATCH(1,IF(HOURS!$B2>=RATES!$B$2:$B$5,IF(HOURS!$B2<=RATES!$C$2:$C$5,IF(HOURS!$A2=RATES!$A$2:$A$5,1))),0))

The following is another version of the above array formula with a more simplified but potentially less readable structure.

=INDEX(RATES!$D$2:$D$5,MATCH(1,(HOURS!$B2>=RATES!$B$2:$B$5)*(HOURS!$B2<=RATES!$C$2:$C$5)*(HOURS!$A2=RATES!$A$2:$A$5),0))

Method 2: Power Query

This method uses Power Query to lookup the correct rate. The heart of this solution is a straightforward merge query with a date filter.

Method 2: Step 1 – Define Named Ranges

In this step, the Hours table and Rates table are given defined names. In Excel, on the Formulas ribbon click Name Manager. Click New… and create a new entry named TBL_HOURS as follows. Click OK when complete.

Excel – Name Manager (TBL_HOURS)
Excel – Name Manager (TBL_HOURS)

Now define a name for the Rates table. Click New… and create a new entry named TBL_RATES as follows. Click OK when complete. Click Close to exit the Name Manager.

Excel – Name Manager (TBL_RATES)
Excel – Name Manager (TBL_RATES)

Method 2: Step 2 – Get Data Into Power Query

On the Data ribbon in Excel, click Get Data and select From Other Sources and then Blank Query. The Power Query Editor opens. From the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties change the query name to PQ_TBL_HOURS. This code brings the data found in the range with defined name TBL_HOURS into Power Query. This query should be set as a Connection Only query.

let
    Source = Excel.CurrentWorkbook(){[Name="TBL_HOURS"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([NAME] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"NAME", type text}, {"DATE", type date}, {"HOURS", type number}})
in
    #"Changed Type"

Next we’ll bring the TBL_RATES data into Power Query. Remaining in the Power Query Editor, from the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties, change the query name to PQ_TBL_RATES. This code brings the data found in the range with defined name TBL_RATES into Power Query. This query should be set as a Connection Only query.

let
    Source = Excel.CurrentWorkbook(){[Name="TBL_RATES"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([NAME] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"NAME", type text}, {"EFFECTIVE START DATE", type date}, {"EFFECTIVE END DATE", type date}, {"RATE", Currency.Type}})
in
    #"Changed Type"

Method 2: Step 3 – Lookup Query

Now we create the lookup query. Remaining in the Power Query Editor, from the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties, change the query name to PQ_TBL_SPEND. Close the Power Query Editor and load PQ_TBL_SPEND to a Table in a New worksheet.

let
    Source = Table.NestedJoin(PQ_TBL_HOURS, {"NAME"}, PQ_TBL_RATES, {"NAME"}, "PQ_TBL_RATES", JoinKind.LeftOuter),
    #"Expanded PQ_TBL_RATES" = Table.ExpandTableColumn(Source, "PQ_TBL_RATES", {"NAME", "EFFECTIVE START DATE", "EFFECTIVE END DATE", "RATE"}, {"PQ_TBL_RATES.NAME", "PQ_TBL_RATES.EFFECTIVE START DATE", "PQ_TBL_RATES.EFFECTIVE END DATE", "PQ_TBL_RATES.RATE"}),
    #"Filtered Rates" = Table.SelectRows(#"Expanded PQ_TBL_RATES", each [DATE] >= [PQ_TBL_RATES.EFFECTIVE START DATE] and [DATE] <= [PQ_TBL_RATES.EFFECTIVE END DATE]),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rates",{{"PQ_TBL_RATES.RATE", "RATE"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "AMOUNT", each [HOURS]*[RATE]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"PQ_TBL_RATES.NAME", "PQ_TBL_RATES.EFFECTIVE START DATE", "PQ_TBL_RATES.EFFECTIVE END DATE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"AMOUNT", Currency.Type}})
in
    #"Changed Type"

The first step in the query performs a Merge between PQ_TBL_HOURS and PQ_TBL_RATES based on the common NAME column. The matching PQ_TBL_RATES entries are added as a column of tables.

Power Query – Merge Query
Power Query – Merge Query

The second step expands the PQ_TBL_RATES column table. For each row in TBL_HOURS and TBL_RATES where the NAME column matched we find corresponding expanded rows. This creates entries where PQ_TBL_HOURS data is duplicated for each date based on the number of entries in PQ_TBL_RATES.

Power Query – Expanded
Power Query – Expanded

To eliminate the rows where the DATE column from TBL_HOURS does not exist between PQ_TBL_RATES.EFFECTIVE START DATE and PQ_TBL_RATES.EFFECTIVE END DATE we apply a filter using SelectRows and the condition [DATE] >= [PQ_TBL_RATES.EFFECTIVE START DATE] and [DATE] <= [PQ_TBL_RATES.EFFECTIVE END DATE]. We find the correct rates are now applied for the particular person and particular date.

Power Query – Filtered Rows
Power Query – Filtered Rows

Rename column PQ_TBL_RATES.RATE to RATE.

Power Query – Renamed Columns
Power Query – Renamed Columns

Add a new column to calculate AMOUNT which is defined as HOURS multiplied by RATE.

Power Query – Add Column
Power Query – Add Column

We no longer need the PQ_TBL_RATES.NAME, PQ_TBL_RATES.EFFECTIVE START DATE, and PQ_TBL_RATES.EFFECTIVE END DATE columns so they are removed.

Power Query – Remove Columns
Power Query – Remove Columns

The final step is to clean up the data types. AMOUNT is changed to the Currency data type.

Power Query – Changed Type
Power Query – Changed Type

Result

The calculated table is loaded to a table on a new worksheet. We see the correct effective rates are applied and the resulting amount is correct.

Excel – Hours, Rate, Amount Table
Excel – Hours, Rate, Amount Table