## 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**

**Recommended Productivity Tools**

**Office Tab**: **Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.**Try now

**Kutools for Excel**: **200 new features for Excel, make Excel much easy and powerful, increase productivity immediately.**Try now

** 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.** Read More** **Free Download 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. You can filter the color cells based on your needs. For example, you want to count the cells whose background is generated by standard formatting (or conditional formatting), you can filter as below:

(1) Click the **Color method** box and then select **Standard formatting **(or** Conditional formatting**) from drop down list;

(2) Click the **Count type** box and select **Background **from drop down list.

**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** **Free Download Kutools for Excel Now**

**Recommended Productivity Tools**

**Office Tab**

** Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.**

**Kutools for Excel**

**Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!**

**200 New Features for Excel, Make Excel Much Easy and Powerful:**

- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

## Comments

Application.Vol atileunde the first line and nothing hapens :(Application.Vol atile is the correct function not Application.vol itile

Where do I find it?

Works in the first row and does not with second and third.

????

The first two lines of my function says:

Function ColorFunction(r Color As Range, rRange As Range, Optional SUM As Boolean)

Application.Volatile

The problem is that only a Alt-Ctrl-F9 will re-calculate. We're looking for a more automated solution. Ideas?

1. be certain you have the function saved as described

2. permit me to use an example

* I have hundreds of rows of data

* I use columns A to AB with more data

* whenever I have an issue with my data, I highlight it in yellow

* I use this cool formula to 'count' the number of highlights on each row

3. How to count the number of my highlights per row

a. figure

the range of cellsthat could have highlights that you want to count (or sum)* for me, I want to COUNT the quantity of my flagged highlights on each row (my range)

b. pick a cell where you will report the count (or sum)

* for me, I placed it on the far right of my data...in column AE

c. insert the following formula in the cell you chose in item b (above)

=colorfunction( AE3,A3:AB3,FALS E)

* for me, I placed this formula in cell AE3 (the end of my row) AND AND I highlighted the same formula cell Yellow

d. I then copied this formula down for all my data rows

4. I observed

a. no counts were made. (bad)

5 KEY ISSUE HERE:

a. I pressed CTL+ ALT + F9

. PRESTO !!! it works.!

hope this banter is of some help.

Cheers

do this:

A B C D

1

2

3

4

If I email you a document can you help me with it?

Mike

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.Applicati on

$excelApp.Quit( )

$excelApp.Visib le = $True

$workbook = $excelApp.Workb ooks.Open("H:\D esktop\test.xls x")#CHANGE THIS TO YOUR EXCEL FILE ADDRESS.

$worksheet = $workbook.Works heets.Item("She et1")#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.Used Range.Rows.Coun t

do{

$currentCell = $worksheet.cell s.item($row, $column)

if($currentCell .text -eq "SEARCH_FOR_THI S") {

$worksheet.cell s.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_THI S" (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:H1 2,"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:H 12,"F")in some cell (say in cell C5-giving you the count of "f:red coloured cells")

3. then use "SUM" : =SUM(C4:C5)wher e 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?

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

U

You then set a countif argument...=COU NTIF(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

I was hoping you would be able to adapt the function for cells coloured using conditional formatting

copy and pasted the formula directly. When I try and use function I get a dialogue box that says "compile error: Expected: end of statement" it also opens VBA and highlights the word "Dim" "Optional SUM As Boolen)

Dim" in the code you pasted.Thanks,

Thank you for sharing the codes on count by colours in such a clear and simple way.

Here is the formula...

=COUNTIFS(CA!A2:A10000,colorfunction(Q3,CA!B2:B10000,FALSE),CA!B2:B10000,">50")

It keeps returning "0" which is wrong, what can to get the real Value?

Hey FRomero,

Well first thing that I see is that you are combining the colorfunction within a CountIF function. Have you established that you are able to attain the desired result by using helper cells. Thus, one cell to determine the value of the Colorfunction and then a second cell to determine the remainder of the CountIF?

I personally have only used a range including up to 1,000 cells. Is the Colorfunction capabale of a range so large? Just some questions that might help you acheive the answer.

later

Ty

Did you ever get an answer that makes this work? I need to do the same thing. I've been trying to modify the colorfunction code itself, but i'm not programmer so I can't get it to work.

Quote: Otherwise, it's amazing. Thanks!!

I'm using Interior.Color instead of Interior.ColorI ndex which gives the correct result.

Very helpful

continue your good work

If there was an addition for the formulas to update automatically when the source data changes that would be totally amazeballs, but otherwise it's pretty cool :))

I would like to count a cell colored only if a data is in it. So if not data in a colored cell, do not take it in account. How can it works?

Thanks

Many thanks for this - I am reporting on cell colours and the above has been useful. However, I am facing a problem whereby when I automatically colour cells using conditional formatting, the above code is not recognising those cells as being coloured. Is this something that can be catered for?

Regarding the VBA code, what if I have merged cells?

I have 62 total, but some of the are merged, my total colored are 27, but I want them to be six as they are merged.

Pls do me favour, if u have Kutool activation key or activator..pls send me on this mail id .

very usefull

thanks a lot

it's great, but why it's not working automatically?

is it possible to make it working automatically?

i hop that, thanks.

This is a wonderful work and helps me a lot.

A1(color black), A2(color blue), A3, (color yellow), A4 (color white), A5 (color white), A6 (color yellow), A7 (color white), A8 (color white), A9 (color blue), A10 (color yellow).

How I can add some cells of the same color with a macro, for example:

A1 = SUM(A2,A9) , A3 = SUM(A4,A5) , A6 = SUM(A7,A8) , A9 = SUM(A10).

=colorfunction(A37;Table_Query;FALSE)

A37 contains the color

any ideas?

Cheers,

Appie

Thanks for this wonderfull module. I applied it and saved. Funny thing is that it returns 0, i'm applying this on a named_range large table of 50.000 rows i can see the colors in the table but nothing happens in the cell that niets to count it other than 0.

Any help would be appreciated.

Appie

=colorfunction(A37;Table_Query;FALSE)

A37 contains the color

any ideas?

Cheers,

Appie

=colorfunction(A37;Table_Query;FALSE)

A37 contains the color

any ideas?

Cheers,

Appie

ColorFunctionI simply get "#NAME?". I am using Excel 360. Can it be that the VBA-formula does not work on this Excel-version? Could someone else please have a try?wow... i just thought.. "excel should be able to do this" and voila

thank you thank you thank you again!

Any help would be great.

thanks!

Thanks for posting!!

Function ColorFunction(r Color 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 = WorksheetFuncti on.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

Thanks!

Works Great. Needed a small tweak though. I have some colored empty cells which are also getting counted right now. How can i ignore those cells?

Thanks

Is there a way to fix this?

I have tried the coding provided. However I am using different color themes, which does not provide me with the answers I am seeking as the coding sums colors of a similar background and I need to obtain the sun for different colors. please assist

Is there any way to change the code so that instead of calculating a SUM when set to TRUE, it will count the number of cells with numbers or text in?? Ideally I would like if it could count a number over 10,000, but just the COUNT function would be great. I have tried simply changing SUM in the code to COUNT, but it doesn't work.

Any help would be great

All the best, mate.

The above function works only if the all the coloured cells are in single sheet .

I am unable to perform the colorfunction sum if the coloured cells are in different sheets.

Any help around this.

EX;- Trying to consolidate the sum of coloured cells in a Master sheet it says #VALUE

I want to capture the data from multiple workbooks.

Please help .

Thanks a Lot.

BR,

RB.

.ColorIndex only supports 256 Colours. And will round similar colours together essentuially.

$100.00 128.66

$76.13 76.13

$450.02 450.02

$28.66 128.66

$544.31 488.77

0 0

$278.66

$636.76 636.76

Please help the numbers on the left are the correct number but when i dragged down the formula i get the numbers on the right. As you can see some of the numbers are correct but others come out wrong. Please help me, i dont understand why this is not working. Thanks.

Fantastic tool and really easy to use so thank you!

Is there any update on using this with conditional formatting? I think there is a fix above but I didn't understand how it worked. Simple instructions for a novice would be much appreciated!

Thanks :)