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. Show
Count and Sum colored cells by Filter and SUBTOTALSupposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column. In this situation, we can filtered the Amount column by color, and then count or sum filtered colored cells by the SUBTOTAL function easily in Excel. 1. Select blank cells to enter the SUBTOTAL function.
Note: In both formulas, E2:E20 is the Amount column containing the colored cells, and you can change them as you need. 2. Select the header of the table, and click Data > Filter. See screenshot: 3. Click the Filter icon in the header cell of the Amount column, and click Filter by Color and the specified color you will count by successively. See screenshot:After filtering, both SUBTOTAL formulas counting and summing all filtered color cells in the Amount column automatically. See screenshot: Note: This method requires the colored cells you will count or sum are in the same column. One click to count, sum, and average colored cells in ExcelWith the excellent Count by Color feature of Kutools for Excel, you can quickly count, sum, and average cells by specified fill color or font color with only one click in Excel. Besides, this feature will also find out the max and min values of cells by the fill color or font color. Full Feature Free Trial 30 -day!Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial30 -day, no credit card required! Get It NowCount or Sum colored cells by GET.CELL functionIn this method, we will create a named range with the GET.CELL function, get the color code of cells, and then count or sum by the color code easily in Excel. Please do as follows: 1. Click Formulas > Define Name. See screenshot: 2. In the New Name dialog, please do as below screenshot shown: 3. Now add a new Color column right to the original table. Next type the formula =NumColor , and the drag the AutoFill handle to apply the formula to other cells in the Color column. See screenshot: Now the color code of each cell in the Amount column returns in the Color Column. See screenshot: 4. Copy and list the fill color in a blank range in the active worksheet, and type formulas next to it as below screenshot shown: Note: In both formulas, $F$2:$F$20 is the Color column, NumColor is the specified named range, $E$2:$E$20 is the Amount Column, and you can change them as you need. Now you will see the cells in the Amount column are counted and sum by their fill colors. Count and sum cells based on specific fill color with User Defined FunctionSupposing the colored cells scatter in a range as below screenshot shown, both above methods cannot count or sum the colored cells. Here, this method will introduce a VBA to solve the problem. 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 background color:
3. Then save the code, and apply the following formula: Note: In above formulas, A is the cell with the particular background color you want to calculate the count and sum, and 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:D11,FALSE) to count the yellow cells. And use the formula =colorfunction(A1,A1:D11,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 and Sum cells based on specific fill color with Kutools FunctionsKutools for Excel also supports some useful functions to help Excel users to make special calculations, says count by cell background color, sum by font color, etc. Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial30 -day, no credit card required! Free Trial Now!1. Select the blank cell you place the counting results, and click Kutools > Kutools Functions > Statistical & Math > COUNTBYCELLCOLOR. See screenshot: 2. In the Function Arguments dialog, please specify the range you will count colored cells within in the Reference box, choose the cell that is filled by the specified background color in the Color_index_nr box, and click the OK button. See screenshot: Notes: Kutools Functions contain a number of built-in functions to help Excel users calculate easily, including Count / Sum / Average Visible cells, Count / Sum by cell color, Count / Sum by font color, Count characters, Count by font bold, etc. Have a Free Trial! Count and Sum cells based on specific fill color with Kutools for ExcelWith 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 300 handy tools for Excel. Full feature free trial30 -day, no credit card required! Free Trial Now!1. Select the range that you want to use, and click Kutools Plus > Count by Color, see screenshot: 2. And in the Count by Color dialog box, please do as below screenshot shown: Note: To count and sum colored cells by specific conditional formatting color, please select Conditional formatting from the Color method drop down list in above dialog, or select Standard and Conditional formatting from the drop down list to count all cells filled by the specified color. Now you will get a new workbook with the statistics. See screenshot: The Count by Color feature calculates (Count, Sum, Average, Max, etc.) cells by background color or font color. Have a Free Trial! Related article:How to count / sum cells based on the font colors in Excel? Demo: Count and sum cells based on background, conditional formatting color:Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now! The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Read More... Free Download... Purchase... Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Read More... Free Download... Purchase...
Oldest First Sort comments by Oldest First Newest First Comments (235) No ratings yet. Be the first to rate! Gowrisankar about 9 years ago #9990 This comment was minimized by the moderator on the site Very useful tool , Thank you verymuch Reply 0 0 Gowrisankar Gowrisankar about 8 years ago #9991 This comment was minimized by the moderator on the site After inserting the function , when we change the value the sum not automatically udpated , any suggestion Reply 0 0 WestwoodMike Gowrisankar about 7 years ago #9992 This comment was minimized by the moderator on the site Alt-Ctrl-F9 will re-calculate Reply 0 0 Mike Brannigan WestwoodMike about 4 years ago #9993 This comment was minimized by the moderator on the site You can add and update button if someone else is using the file and they don't know about the Alt-Ctrl-F9. just add this line of code to the macro "Application.CalculateFull". it's that same as Alt-Ctrl-F9 just programmed into a button. Reply 0 0 Lucas Mike Brannigan about 4 months ago #39492 This comment was minimized by the moderator on the site En que parte agrego la linea para crear el boton Reply 0 0 12345678998765432154 Gowrisankar about 6 years ago #9994 This comment was minimized by the moderator on the site Add a second line directly below the first that says Application.volitile it makes it recalculate after something is updated Reply 0 0 Jarod 12345678998765432154 about 6 years ago #9995 This comment was minimized by the moderator on the site I tried to put Application.Volatile unde the first line and nothing hapens :( Reply 0 0 abrugg 12345678998765432154 about 6 years ago #9996 This comment was minimized by the moderator on the site [quote]Add a second line directly below the first that says Application.volitile it makes it recalculate after something is updatedBy 12345678998765432154[/quote] Application.Volatile is the correct function not Application.volitile Reply 0 0 Jo abrugg about 6 years ago #9997 This comment was minimized by the moderator on the site I don't see any line that says application.volatile. Where do I find it? Reply 0 0 Jacqui Gowrisankar about 5 years ago #9998 This comment was minimized by the moderator on the site I double clicked the cell with the ColorFunction formula, and then pressed Enter. It updated. Reply 0 0 HR Gowrisankar about 8 years ago #9999 This comment was minimized by the moderator on the site Very useful. Thank you Reply 0 0 Aniko HR about 6 years ago #10000 This comment was minimized by the moderator on the site I also tried the formula. Works in the first row and does not with second and third. ???? Reply 0 0 Mark Aniko about 5 years ago #10001 This comment was minimized by the moderator on the site I agree with Jarod. The first two lines of my function says: Function ColorFunction(rColor 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? Reply 0 0 Mark Aniko about 5 years ago #10002 This comment was minimized by the moderator on the site Try it like this: 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,FALSE) * 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 Reply 0 0 Petr about 9 years ago #10009 This comment was minimized by the moderator on the site Very good, thanks a lot !!! Reply 0 0 imamoglu about 9 years ago #10044 This comment was minimized by the moderator on the site Very useful article, thanks a lot Reply 0 0 Laxmannarayanan about 9 years ago #10052 This comment was minimized by the moderator on the site Wonderful!!! Thank you so much!!! Reply 0 0 Shankee about 9 years ago #10058 This comment was minimized by the moderator on the site Amazing,thanks a lot Reply 0 0 David Coe about 9 years ago #10099 This comment was minimized by the moderator on the site This is great tool, thanks for sharing! I do have one question: I noticed that the macro formula does not update itself when you change a cell color from one color to another. Is there a way to get it to automatically update when cell colors change? If I double click on the cell with the addition formula and press enter, it updates, but I have a sheet with many of these cells and don't want to have to manually update it each time. Reply 0 0 skyyang David Coe about 9 years ago #10100 This comment was minimized by the moderator on the site Thank you for your reply, I’m sorry, under the current situation, the VBA code can’t solve the question that you pointed out, you need to update them manually. Reply 0 0 Jako skyyang about 6 years ago #10101 This comment was minimized by the moderator on the site I have also found that using the format painter to update the cell color will cause the formulas to work as expected. Still not perfect. Reply 0 0 Happiqiu David Coe about 8 years ago #10102 This comment was minimized by the moderator on the site Hi, i have tried. and the worksheet just needs an "Enter" No matter which cell. So, after coloring your cell, just type a memo on the colored cells and "enter" Reply 0 0 Louise David Coe about 8 years ago #10103 This comment was minimized by the moderator on the site try CTL+ALT+f9 it will update all cells with addition formulas at once. Not quite automatic but at least better than having to click on each cell with addition formula individually. Reply 0 0 ollie Louise about 7 years ago #10104 This comment was minimized by the moderator on the site this did nothing for me :( Reply 0 0 Sankar Narayanan about 9 years ago #10120 This comment was minimized by the moderator on the site Hi, getting error. complie error, ambiguous error is coming. Reply 0 0 RAMON about 9 years ago #10123 This comment was minimized by the moderator on the site I applied the formula but the cell said NAMES? what I need to fix Reply 0 0 skyyang RAMON about 9 years ago #10124 This comment was minimized by the moderator on the site Hello RAMON, maybe you didn’t copy the above code into the Module. You must copy the code into the Module first and save it, then apply the formulas. You can try it. If it doesn’t help you, please let me know. Reply 0 0 Alexandra skyyang about 9 years ago #10125 This comment was minimized by the moderator on the site Hello, It worked the first time, but now, when I have to add a cell to the sum... I tried introducing again the code, saving, writing the formula again... it says "name?" Reply 0 0 Daria Alexandra about 8 years ago #10126 This comment was minimized by the moderator on the site I'm having the same problem -- worked beautifully at first, but getting the NAME error after adding another cell. Have tried to delete module and add again, re-enter the function...no joy. Reply 0 0 Gary_9991 Daria about 8 years ago #10127 This comment was minimized by the moderator on the site You might need to enable Marcos again. Should flash up at the top of the screen depending on version of excel. :) Reply 0 0 Mike F skyyang about 6 years ago #10128 This comment was minimized by the moderator on the site Skyyang, If I email you a document can you help me with it? Mike Reply 0 0 AvGravy about 9 years ago #10143 This comment was minimized by the moderator on the site Thank you so much! This is genius, thanks for the help! Reply 0 0 aliinmegeve about 9 years ago #10146 This comment was minimized by the moderator on the site Thank you SO much, i have been looking all day for a successful outcome and we now have one :) Reply 0 0 Kertis about 9 years ago #10151 This comment was minimized by the moderator on the site Thanks. It worked for me as described. However, after I reopened the file next time all cells with this formula were showing error. I had to re-copy the coding again. Is there something I am missing? I will need to share the file with others and they wont be able to "fix" the problem. Thanks, Ker. Reply 0 0 Happiqiu Kertis about 8 years ago #10152 This comment was minimized by the moderator on the site You need to save it as excel Macro workbook Reply 0 0 libin about 9 years ago #10153 This comment was minimized by the moderator on the site well done..but..how it works in conditional formatting cell colors ??? Reply 0 0 Ceza about 9 years ago #10166 This comment was minimized by the moderator on the site This is awesome, thanks! Reply 0 0 chaminda about 9 years ago #10188 This comment was minimized by the moderator on the site it worked well done!!! Reply 0 0 Ali about 9 years ago #10189 This comment was minimized by the moderator on the site dear sir , 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 Reply 0 0 Anith about 9 years ago #10223 This comment was minimized by the moderator on the site Thanks Sir, This is a wonderful work and helps me a lot. Reply 0 0 James B about 9 years ago #10251 This comment was minimized by the moderator on the site I have an Excel file with many coloured cells. Is there any way to convert these colours to data? So a red cell will have ''red'' in it as data, a blue coloured cell will have ''blue'' in it and so on? James Reply 0 0 sri rangavalli about 9 years ago #10255 This comment was minimized by the moderator on the site Timely and apt article like hitting the target which am looking for:) thanks Reply 0 0 Ian about 9 years ago #10268 This comment was minimized by the moderator on the site I can't seem to get this to work. Does it only work for cells that have been coloured manually? I need to count cells that have been coloured via Conditional Formatting, but it's not playing ball at the moment. Reply 0 0 Ryan Ian about 9 years ago #10269 This comment was minimized by the moderator on the site I am having the same issue as Ian, I am trying to sum numbers based on the color rules set by Conditional Formatting but this doesnt seem to catch that. Any Suggestions? Reply 0 0 Admin_jay Ryan about 8 years ago #10270 This comment was minimized by the moderator on the site We will try to enhance it in the upcoming versions. :-) Reply 0 0 Jen Ryan about 8 years ago #10271 This comment was minimized by the moderator on the site I threw a Powershell script that acts as a kind of work-around: " #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! Can you count in Excel based on cell color?To count cells of a specific color in a given range, carry out these steps: Insert the code of the CountCellsByColor function in your workbook. In a cell where you want the result to appear, start typing the formula: =CountCellsByColor( For the first argument, enter the range in which you want to count colored cells.
How do I count cells based on conditional formatting colors?Select a range you want to count or sum cells by background color with conditional formatting, then click Kutools Plus > Count by Color.
How do you sum if a cell is green?Assign a number for each background color. Click the Formulas tab, then select Name Manager. ... . In the New Name dialog box, enter “Color” for Name and the formula.. Enter the color number for each background color in column F: In cell F3, enter the value “6” for yellow, while in cell F4, enter the value “10” for green.. |