To apply a simple filter, click the Filter dropdown for the Invoice Amount column. Next, select Filter By Color and select yellow (Figure D). This submenu shows only a few of the colors; to see the. In the Color box, select Red. In the Font style box, select Bold. Click OK until the dialog boxes are closed. The formatting is applied to column.

While working with Excel, we are able to sum values that satisfy a given criteria by using the SUMIF function. This step by step tutorial will assist all levels of Excel users in summing values in cells based on background color.

Figure 1. Final result: Using SUMIF to sum cells based on background color

Final formula: =SUMIF($D$3:$D$8,F3,$C$3:$C$8)

Syntax of the SUMIF Function

SUMIF sums the values in a specified range, based on one given criteria

=SUMIF(range,criteria,[sum_range])

The parameters are:

• Range: the data range that we will evaluate using the criteria
• Criteria: the criteria or condition that determines which cells will be added
• 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

Setting up the Data

Our table has three columns: Product ID (column B), Orders (column C) and a helper column Background Color (column D). Note that Product ID and Orders have preset background colors yellow and green.

Cells F3 and F4 contain the two background colors yellow and green, which will serve as our criteria. We want to determine the sum of the orders based on the background color. We enter the results in cells G3 and G4.

Figure 2. Sample data to sum cells based on background color

There’s no straightforward way to sum cells based on background color in Excel. 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.

Assign a number for each background color

There is a built-in function in Excel, the GET.CELL function, that returns a unique number for each background color in a cell. However, it cannot be entered directly as a worksheet function. Instead, it is used within a named range. Let us follow these steps:

Step 1. Select cells D3:D8

Step 2. Click the Formulas tab, then select Name Manager. This will launch the Name Manager dialog box.

Figure 3. Create a new named range

Step 3. Click New

Step 4. In the New Name dialog box, enter “Color” for Name and the formula

=GET.CELL(38,Sheet1!C3) in the Refers to bar.

Figure 4. Entering the GET.CELL function to a new named range “Color”

This formula returns the color number unique for the background color in the adjacent cell C3.

Step 5. Click OK, then CLOSE.

Figure 5. Named range “Color” created in Name Manager

Step 6. Select cell D3 and enter the formula: =Color

As a result, the value “6” is returned in cell D3, which is the color number for the background color yellow used in cell C3.

Figure 6. Alesis multimix 16 firewire driver for mac. Assigning a number for background color of C3 using the named range “Color”

Step 7. Copy the formula in cell D3 to cells D4:D8. Below figure shows column D that is filled with the corresponding color number for the background color in column C.

Figure 7. Assigning a number for each background color using the named range “Color”

Step 8. 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.

Figure 8. Assigning a number for each background color as criteria

Update mac from 10.11.6. 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. Let us follow these steps:

Step 1. Select cell G3

Step 2. Enter the formula: =SUMIF($D$3:$D$8,F3,$C$3:$C$8)

Step 3. Press Enter

Step 4. Copy the formula in cell G3 to cell G4.

The range we want to evaluate is D3:D8, which contains the number for the background color in column C. Our criteria in cell F3 is “6”, or the yellow color. We want to sum the orders in C3:C8 that satisfy the given criteria.

The products that satisfy our criteria and have yellow background color are cells C3, C5 and C6. As a result, the sum of orders in cell G3 is $ 3,700.

Figure 9. Entering the formula using SUMIF to sum cells based on background color

Copying the formula in cell G3 into cell G4 returns the value $ 7,500, which is the sum of orders with green background color.

Figure 10. 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. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Count & Sum by Color is a nifty app for Mac that quickly calculates cells by the color of background or font. From this help page, you'll learn how to use the All Colors and One Color tabs on the app's pane to sum or count highlighted cells, as well as find average, min and max values based on the color code. Below you'll also find how to paste the calculation results to any location in your table.

How to work with the All Colors tab

Count & Sum by Color app will become your reliable assistant in adding or counting cell values grouped by colors. If you are fond of using different color codes to highlight certain parts of data, this helpful app will show you Sum, Count, Average, Min or Max for the colored cells.

1. If you need to sum or count all highlighted cells in Excel 2011, first open the worksheet with your data and run the Count & Sum by Color app by clicking on its name under the Ablebits.com tab:
   
2. You'll see the Count & Sum by Color pane open. Click on the All Colors tab:
   
3. Highlight the needed range in your table and you will see its address in the Select range field:
   It's also possible to click on the Select range button to pick the necessary area manually:
   
4. Now you can specify the color option by selecting the Background or Font radio button:
   
   • Press the Background radio button to calculate by the color of cells.
   • Select the Font radio button to process the selected values by their text color.
5. Press the Calculate button to process the range:
   

   The results will be displayed on the Count & Sum by Color pane grouped by color codes:
   

6. The app will quickly perform all math operations on your data. You can then pick any function from the Show results for drop-down list to see the needed calculation results:
   
   • Sum - add all the cell values.
   • Count - count the number of cells.
   • Average - determine the average of the values.
   • Min - see the highest number.
   • Max - show the lowest cell value.
7. This handy calculating tool also lets you paste the results from the app's pane to any location on your spreadsheet.

   Click on the Paste results.. button to see the Ablebits.com Count & Sum by Color dialog box:
   Select any cell in your current or new worksheet for adding the numbers and click OK to see the calculation results added:
   

How to work with the One Color tab

If you use multiple different colors to group and highlight data in your Excel 2011 tables, you may need to pick a certain color and use it to calculate cell values. The One Color tab on the Count & Sum by Color pane lets you instantly calculate cell data based on the color you select.

1. Open the Excel table where you need to calculate cell values and run the Count & Sum by Color app by clicking on its name under the Ablebits.com tab:
   
2. Click on the One Color tab to start:
   
3. Select the range in your table and you will see its address in the Select range field:
   You can also click on the Select range button to pick the necessary range manually:
   
4. Now you need to specify the correct color by choosing the cell that contains the necessary fill or font in your table. To do this, click on the Select range button next to the Pattern cell field:
   You'll get the Ablebits.com Count & Sum by Color dialog box that lets you select a pattern cell with the right color:
   The address of the selected cell will automatically appear in the Pattern cell field.
5. Define if you want to use the Background or Font color for calculation:
   
   • Click the Background radio button to count the selected data by the fill color.
   • Select Font if it's the text color you want to use.
6. Finally, press the Calculate button:
   On the add-in pane you'll instantly see Sum, Count, Average, Min and Max calculated for the cell data of the specified color:
   
7. If you want to paste the results to your table, just click on the Paste results.. button:
   You'll get the Ablebits.com Count & Sum by Color dialog box letting you pick the top left cell for pasting the calculations. Just click on the needed cell and click OK on the dialog box:
   Feel free to use the One Color tab each time you need to see the results just for the color you specify.

   The Count & Sum by Color app will save you much time on calculating and recalculating highlighted data in your Excel for Mac. Besides summing and counting values in the colored cells, it also allows applying Average, Max or Min functions to the highlighted range. Choose this tool for quick and reliable color calculations.