## How to count and sum cells based on background color in Excel?

Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color.

In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.

**Count and sum colored cells with User Defined Function**

**Count/Sum and do other calculations of colored cells with Kutools for Excel**

**Kutools for Excel**: add 120 new features in Excel. Save one hour every day.

**Classic Menu for Office**: brings back classic menus to Office 2010 and 2013 (includes Office 365).

**Office Tab**: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

** Count and sum colored cells with User Defined Function**

The following code can help you count and sum the cells with a certain background color, please do as this:

**1**. Hold down the **ALT + F11** keys, and it opens the **Microsoft Visual Basic for Applications** window.

**2**. Click **Insert** >** Module**, and paste the following code in the Module Window.

**VBA: Count and sum cells based on backgroud color.**

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function

**3**. Then save the code, and apply the following formula:

**Count the colored cells**: **=colorfunction(A,B:C,FALSE)**

**Sum the colored cells**: **=colorfunction(A,B:C,TRUE)**

**A**: is the cell with the particular background color you want to calculate the count and sum.

**B:C**: is the cell range where you want to calculate the count and sum.

**4**. Take the following screenshot for example, enter the formula **=colorfunction(A1,A1:D7,FALSE)** to count the yellow cells. And use the formula** =colorfunction(A1,A1:D7,TRUE)** to sum the yellow cells. See screenshot:

**5**. If you want to count and sum other colored cells, please repeat the step 4. Then you will get the following results:

** Count/Sum and do other calculations of colored cells with Kutools for Excel**

With the above User Defined Function, you need to enter the formula one by one, if there are lots of different colors, this method will be tedious and time-consuming. But if you have **Kutools for Excel**’s **Count by Color** utility, you can quickly generate a report of the colored cells. You not only can count and sum the colored cells, but also can get the average, max and min values of the colored range.

**Kutools for Excel** includes more than 120 handy Excel tools. Free to try with no limitation in 30 days.**Get it Now**.

If you have installed Kutools for Excel, please do as following steps:

**1**. Select the range that you want to use.

**2**. Click **Enterprise** > **Count by Color**, see screenshot:

**3**. And in the **Count by Color** dialog box, all of the colors have been counted.

**4**. And then click **Generate report** button, you will get a new workbook with the statistics. See screenshot:

If you want to know more about this feature, please click **Count by Color**.

## Comments

Thanks, Ker.

in fact it is wonderful code but I faced a problem that it will continue calculate the cell even when we change the cell color

This is a wonderful work and helps me a lot.

Any Suggestions?

"

#setup Excel

$excelApp = New-Object -comobject Excel.Application

$excelApp.Quit()

$excelApp.Visible = $True

$workbook = $excelApp.Workbooks.Open("H:\Desktop\test.xlsx")#CHANGE THIS TO YOUR EXCEL FILE ADDRESS.

$worksheet = $workbook.Worksheets.Item("Sheet1")#CHANGE THIS IF YOU SHEET ISN'T CALLED "Sheet1"

#static variables

$row = 1

$column = 1#CHANGE THIS VARIABLE TO WHICHEVER COLUMN YOU'RE SEARCHING

$totalRow = $worksheet.UsedRange.Rows.Count

do{

$currentCell = $worksheet.cells.item($row, $column)

if($currentCell.text -eq "SEARCH_FOR_THIS") {

$worksheet.cells.item($row,$column).Interior.ColorIndex = 44#CHANGE THIS NUMBER TO CHANGE THE NEW-CELL COLOUR

"$row, $column = BLANK. Colouring"

}

$row++

$row

}

while($row -lt $totalRow)

"Script Complete."

"SAVING..."

$excelApp.Save

$excelApp.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp)

"

This script will search through your spreadsheet and if it finds a cell that contains "SEARCH_FOR_THIS" (you should probably change that part in the script) then it will turn that cell to whatever colour number is in the script (currently set to 44, a weird looking orange/tan colour). Once the script has finished it'll try and tell Excel that it wants to save, but Excel isn't a very trusting program so will ask for you to click on save. Once you've done that then it'll close down. Then, open the file back up and do all that business that listed on this page, making sure that the colour that my script it looking for it the same colour that the script listed on this page it looking for.

Hope this helps!

i can suggest you an alternative: try using "SUM"

1.intially consolidate the count of different colored cells ,

eg :in my case i used conditional formatting to "if p-equal to green color) "(say (h4:h7)ranged cells) and then used to count using formula =COUNTIF(H10:H12,"P")in some cell (say in cell C4-giving you the count of "p:green coloured cells")

2.then similarly "if f-equal to red color) "(say (h4:h7)ranged cells) and then used to count using formula =ColorFunction(B5,H10:H12,TRUE)+COUNTIF(H10:H12,"F")in some cell (say in cell C5-giving you the count of "f:red coloured cells")

3. then use "SUM" : =SUM(C4:C5)where c4 = count of p:green coloured cells + c5 = f:red coloured cells

hope it helps you:)

This is Abdullah Sarker.I faced one problem in excel.Please suggest me.

Problem: How to count Or Sum Only color cell in excel.

Thanks a lot

BUT

can I ask if this can be taken a step further....

I want to look at a range of cells and only count the number of cells which contain certain text (e.g 2 letters)AND are filled with a certain colour.

Can you help?

If I have in yelow cells for example dates and I want to count these yelow cells between 1.1.2014 and 1.2.2014?

How it should looks like?

Thank you...

And then please apply count by color.

How can I save this code and apply the following formula?

If I change the cell color using another formula and then ask to calculate sum up with the above formula then it doesn't work. Can you please help me resolving the problem?

I need to count the number of cells within a range of cells which....

1. contain text which match the text in a particular cell,

AND

2. has a background colour that match another particular cell.

I need the counting to be automatic as I change the text or colour of the cells within the range of cells.

Can anyone lease help?

http://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html#comment-730

U

You then set a countif argument...=COUNTIF(H7:H11,"r") to capture the number of values r in cells h7 through h11.....very simple but works very very well.

Two items to add:

1. A way to update when cell colors change - from what I see here it looks like I will need to have an additional macro button to update the calculation.

2. A way to calculate cells whose color changes based on conditional formatting.

If it had both these two elements it would be a golden piece of code.

Sorry very inexperienced at excel

But thanks for any help