Assigning a number for each background color using the named range “Color” Below figure shows column D that is filled with the corresponding color number for the background color in column C.įigure 7. Copy the formula in cell D3 to cells D4:D8. Assigning a number for background color of C3 using the named range “Color” Select cell D3 and enter the formula: =ColorĪs a result, the value “6” is returned in cell D3, which is the color number for the background color yellow used in cell C3.įigure 6. Named range “Color” created in Name Manager This formula returns the color number unique for the background color in the adjacent cell C3.įigure 5. Entering the GET.CELL function to a new named range “Color” =GET.CELL(38,Sheet1!C3) in the Refers to bar.įigure 4. In the New Name dialog box, enter “ Color ” for Name and the formula This will launch the Name Manager dialog box. Click the Formulas tab, then select Name Manager. Instead, it is used within a named range. However, it cannot be entered directly as a worksheet function. There is a built-in function in Excel, the GET.CELL function, that returns a unique number for each background color in a cell. Assign a number for each background color For this example, the key is to assign a value for each background color, and use that value as the criteria for our SUMIF function. There’s no straightforward way to sum cells based on background color in Excel. Sample data to sum cells based on background color We enter the results in cells G3 and G4.įigure 2. We want to determine the sum of the orders based on the background color. Note that Product ID and Orders have preset background colors yellow and green.Ĭells F3 and F4 contain the two background colors yellow and green, which will serve as our criteria. Our table has three columns: Product ID (column B), Orders (column C) and a helper column Background Color (column D). Sum_range : the cells that will be added if left blank, “sum_range” = “range” which means that the range of data that will be added is the same range of data evaluated.Criteria : the criteria or condition that determines which cells will be added.Range : the data range that we will evaluate using the criteria.SUMIF sums the values in a specified range, based on one given criteria Final result: Using SUMIF to sum cells based on background colorįinal formula: =SUMIF($D$3:$D$8,F3,$C$3:$C$8) Syntax of the SUMIF Function This step by step tutorial will assist all levels of Excel users in summing values in cells based on background color.įigure 1.
While working with Excel, we are able to sum values that satisfy a given criteria by using the SUMIF function. How to Use SUMIF to Sum Cells Based on Background Color
0 kommentar(er)
