How to Count or Sum Cells With Certain Color in Excel

This tutorial will demonstrate you how to count or sum cells of a certain background color using VBA.

count sum cell color Main Function

Consider the following range in Excel.

count sum cell color 01

There is no built in Excel function to count colored cells. Instead we must create a User-Defined Function using VBA.

Count Cells – Create a VBA Custom Function

Counting how many cells are of a certain color using VBA is a matter of creating a user-defined function (UDF) that will loop through all the cells in the range, and determine if the background color of each cell matches the background color that you are testing for, and then using that function in the Excel worksheet.

In VBA, we create a function to count selected cells.

Then use this function in the worksheet to return the value.

  1. Click in the orange cell in G4, and click Insert Function.

STEP 01

  1. Select User Defined as the category, and then select CountCellsByColor as the function to use.

STEP 02

  1. Click OK.

STEP 03

  1. Highlight the range that contains all the colored cells.

STEP 04

  1. Select the ColorCell, and then Click OK.
    STEP 05

Repeat the process to Count the cells with a Green Background color.

Sum Cells – Create a Custom VBA Function

We create a similar custom function in VBA to sum the values of the cells of a certain color.

We would then once again use this function in the Worksheet to sum up the required cells.

count sum cell color Final

 

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!