#Excel for mac shortcut to apply fill color full
Full feature free trial 30-day, no credit card required! Free Trial Now!ġ. Kutools for Excel- Includes more than 300 handy tools for Excel. 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. Then you will get the following results:Ĭount and Sum cells based on specific fill color with Kutools Functions If you want to count and sum other colored cells, please repeat the step 4. And use the formula =colorfunction(A1,A1:D11,TRUE) to sum the yellow cells. Take the following screenshot for example, enter the formula =colorfunction(A1,A1:D11,FALSE) to count the yellow cells. 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.Ĥ. Sum the colored cells: =colorfunction(A,B:C,TRUE) Count the colored cells: =colorfunction(A,B:C,FALSE)ī. Then save the code, and apply the following formula:Ī. VResult = WorksheetFunction.SUM(rCell, vResult)ģ. VBA: Count and sum cells based on background color: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
#Excel for mac shortcut to apply fill color code
Click Insert > Module, and paste the following code in the Module Window. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.Ģ.
Here, this method will introduce a VBA to solve the problem.ġ. Supposing the colored cells scatter in a range as below screenshot shown, both above methods cannot count or sum the colored cells. Now you will see the cells in the Amount column are counted and sum by their fill colors.Ĭount and sum cells based on specific fill color with User Defined Function 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. To sum cells by color, please enter the formula =SUMIF($F$2:$F$20,NumColor,$E$2:$E$20). To count cells by color, please enter the formula =COUNTIF($F$2:$F$20,NumColor) ī. Copy and list the fill color in a blank range in the active worksheet, and type formulas next to it as below screenshot shown:Ī. Now the color code of each cell in the Amount column returns in the Color Column. You need to change it to the specified name you set. Note: In the formula, NumColor is the named range we specified in the first 2 steps. Next type the formula =NumColor, and the drag the AutoFill handle to apply the formula to other cells in the Color column. Now add a new Color column right to the original table. (2) Enter the formula =GET.CELL(38,Sheet4!$E2) in the Refers to box ( note: in the formula, 38 means return the cell code, and Sheet4!$E2 is the first cell in the Amount column except the column header which you need to change based on your table data.)ģ. In the New Name dialog, please do as below screenshot shown: In 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. Note: This method requires the colored cells you will count or sum are in the same column. See screenshot:Īfter filtering, both SUBTOTAL formulas counting and summing all filtered color cells in the Amount column automatically. 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. Select the header of the table, and click Data > Filter. Note: In both formulas, E2:E20 is the Amount column containing the colored cells, and you can change them as you need.Ģ. To sum all cells with the same background color, please enter the formula =SUBTOTAL(109, E2:E20).To count all cells with the same background color, please enter the formula =SUBTOTAL(102, E2:E20).Select blank cells to enter the SUBTOTAL function. 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.ġ. Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column.
0 kommentar(er)
