What we want now is automatically count cells by color, i. As I explained above, there is no straightforward solution to this task. But luckily we have very skilled and knowledgeable Excel gurus in our team and one of them has written the faultless code for Excel , and So, move on with the 5 quick steps below and you will know the number and sum of your color cells in a few minutes.
If you are not very comfortable with VBA, you can find the detailed step-by-step instructions and a handful of useful tips in this tutorial: How to insert and run VBA code in Excel. Now that all "behind the scenes" work is done for you by the just added user-defined function, choose the cell where you want to output the results and enter the CountCellsByColor function into it:. In a similar way, you write the formula for the other colors you want to count, yellow and green in our table.
If you have numerical data in colored cells e. The VB script below was written in response to Connor's comment also by our Excel's guru Alex and does exactly what Connor requested, namely counts and sums the cells of a certain color in all worksheets of the workbook.
So, here comes the code:. Simply enter either formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color in brackets, e. Here you will find a summary of all the functions we've used in this example as well as a couple of new ones that retrieve color codes.
You can use all other formulas listed below in a similar way. CountCellsByFontColor range, color code - counts cells with the specified font color. Formulas to sum by color: SumCellsByColor range, color code - calculates the sum of cells with a certain background color. SumCellsByFontColor range, color code - calculates the sum of cells with a certain font color.
Formulas to get the color code: GetCellFontColor cell - returns the color code of the font color of a specified cell. GetCellColor cell - returns the color code of the background color of a specified cell. Well, counting cells based on color and getting the sum of colored cells was pretty easy, wasn't it?
Of course if you have that little VBA gem that makes the magic happen : But what if you do not color cells manually and rather use conditional formatting, as we discussed in these two articles How to change the background color of cells and How to change a row's color based on cell value?
If you have applied conditional formatting to color cells based on their values and now you want to count cells by color or sum the values in colored cells, I have bad news - there is no universal user-defined function that would sum by color or count color cells and output the resulting numbers directly in the specified cells. CELL 38,Sheet4! Now add a new Color column right to the original table. See screenshot: Note : In the formula, NumColor is the named range we specified in the first 2 steps.
You need to change it to the specified name you set. Now the color code of each cell in the Amount column returns in the Color Column. Copy and list the fill color in a blank range in the active worksheet, and type formulas next to it as below screenshot shown: A. Now you will see the cells in the Amount column are counted and sum by their fill colors.
Supposing 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. Then save the code, and apply the following formula: A. 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.
If you want to count and sum other colored cells, please repeat the step 4. Then you will get the following results:. Kutools 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.
Free Trial Now! Have a Free Trial! 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. You not only can count and sum the colored cells, but also can get the average, max and min values of the colored range.
And in the Count by Color dialog box, please do as below screenshot shown: 1 Select Standard formatting from the Color method drop down list; 2 Select Background from the Count type drop down list. 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.
Quick Excel Help. Get instant live expert help with Excel or Google Sheets. Post your problem and you'll get expert help in seconds Your message must be at least 40 characters. Our professional experts are available now. Your privacy is guaranteed. Connect to an Expert. Let us follow these steps: Step 1. Select cells D3:D8 Step 2. Create a new named range Step 3. Click New Step 4. Step 5. Assigning a number for each background color as criteria Sum of orders based on background color Now that each background color has a corresponding color number, we can easily sum the orders based on background color by using the SUMIF function.
Select cell G3 Step 2. Press Enter Step 4. Copy the formula in cell G3 to cell G4. Output: Summing cells using SUMIF based on background color Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. This comment form is under antispam protection. Subscribe to Excelchat. Trusted by people who work at. To find out more, see here: Cookie Policy.
Ok, Got It! Got it! Your question will be answered by an Excelchat Expert. Hang tight for 30 secs while we redirect you. Save time by asking instead!
0コメント