Excel Tips Sharing: Three methods to sum based on cell fill colors

In the work process, sometimes in order to easily distinguish different categories, we usually choose to color the cells. This method is simple and quick. So what if you want to summarize based on cell color later? We all know that we can filter by cell color, so besides the simplest filter, what other methods are there? Today I will introduce to you several methods of summing cell colors in Excel.

As shown in the figure, the number of orders is summed according to four different colors according to the following cases.

1. Search and Sum

Everyone often uses the search function, but does everyone use it to search based on color? The specific method is as follows:

Click "Find" under "Find and Select" in the [Edit] group under the Home tab or press Ctrl F to open the "Find and Replace" window.

Click "Options" in the "Find and Replace" window. A "Format" drop-down box will appear above the options. Select "Select format from cell" in the drop-down box. You can also directly select the format to set, but it is of course more convenient to select from the cell.

#The mouse will turn into a straw. After clicking on the yellow cell, the preview pane next to the format will be yellow. Click "Find All" and all yellow cells will appear below.

Click on any record found below, hold down Ctrl A, and all yellow cells will be selected. All yellow sums appear in the lower right corner of the worksheet.

Then use this method to get the summed values ​​of cells of other colors in turn.

This method is simple and easy to operate. The disadvantage is that it can only be operated one by one according to the color.

2. Macro table function summation

In Excel, you can use the macro table function get.cell to get the fill color of the cell. However, the macro table function must have a customized name before it can be used. The specific method is as follows:

Click "Define Name" in the [Defined Name] group under the Formula tab.

In the "Edit Name" window, enter "color" for the name and "=GET.CELL(63, macro function!B2)" for the reference location. "Macro table function" is the name of the worksheet where it is located. Since the formula is first entered in cell C2 to obtain the color value, the colored cell B2 is selected here. Without adding an absolute reference, it is convenient to obtain the color value of the left cell in other cells.

Then enter "=color" in cells C2:C10. The value in this column is the color value.

Similarly, enter the color value "=color" next to the color column F2:F5.

Finally, use the SUMIF function "=SUMIF(C:C,F2,B:B)" according to the one-to-one corresponding color value.

Use the macro table function to obtain the color value, and then use the SUMIF function to sum it. In addition to using the SUMIF function, this method of obtaining color values ​​can also use other different functions to analyze colors from multiple angles, which is very convenient and practical.

3. VBA summation

The most convenient and fastest way to get the cell color is of course to use VBA. The functions included in Excel itself cannot implement summing by color. We use VBA to build a custom function to help implement summing by color.

Hold down Alt F11 or right-click on the worksheet tab and "View Code" to open the VBA editor.

In the VBA editor, click "Module" below to insert.

Click on the newly created module--Module 1, and enter the following code in the right window.

Function SumColor(col As Range, sumrange As
Range) As Long
    Dim icell As Range
    Application.Volatile
    For Each icell In sumrange
        If
icell.Interior.ColorIndex = col.Interior.ColorIndex
Then
           
SumColor = Application.Sum(icell) + SumColor
       
End If
    Next icell
End Function

Analysis:

SumColor is a custom function name, which includes two parameters. The first parameter col is the cell to obtain the color, and the second parameter sumrange is the summation area.

(This is equivalent to creating a function SumColor ourselves and defining the meaning of the two parameters of the function. For beginners, you don’t need to understand the meaning of this code for the time being, you just need to save it as Just apply the template)

Click "File"-"Save", and then close the VBA editor directly.

After the custom function is defined, it can be used directly in the worksheet. Just enter "=SumColor(E2,$A$2:$B$10)" in cells F2:F5.

Note: Due to the use of macros, macro table functions and VBA usage can be saved directly in the EXCEL2003 version, but versions above 2003 need to be saved in the "xlsm" format for normal use.

For color-marked cells, this method is easy to use but not applicable to many scenarios. VBA is very powerful, but you need to go deeper to fully understand it. of learning. The macro table function method is relatively simple and practical. If you find it useful, please save it!

Related learning recommendations: excel tutorial

The above is the detailed content of Excel Tips Sharing: Three methods to sum based on cell fill colors. For more information, please follow other related articles on the PHP Chinese website!