Shulou(Shulou.com)11/24 Report--

The original title: "Sum according to color, learn these four tricks, go all over the world are not afraid!" "

The world is colorful, colorful and colorful!

The colors in the Excel table are no exception.

The use of colors in the table looks very eye-catching and intuitive to increase the sense of beauty!

But after marking some cells with color in the table, if you want to sum these colored cells (or average, maximum, etc.).

It's a tricky and troublesome thing!

As shown in the figure:

Here are just a few simple examples of data for easy explanation. No matter how much data, the method is similar! If you don't know how to do it, you can only add it together, such as:

= sum (C2, C4, C6, C8) or:

= C2+C4+C6+C8 either enter the cell address manually or click with the mouse.

If there is a lot of data, it is not only very inefficient, but also may make mistakes, so don't push people to use this method!

Is there any other way to quickly and accurately count the values in colored cells?

Of course there is. Let's take a look at four ways to sum by color.

Efficient, and not easy to make mistakes!

Recommendation index of auxiliary column method: ★

Degree of difficulty: ★☆☆☆☆

Suitable for scenarios: in the case of single color or not many colors

Applicable version: all version

This method is not only suitable for color summation, in many cases, the problem or function formula can be simplified, thus the complexity will be simplified, and it will be impossible!

❶ first filters the C column data by cell color, and filters out the colored cells.

❷ adds an auxiliary column to column D, and then writes it all with 1, as shown in the following figure:

❸ unfilters and writes the formula in the E1 cell, the formula:

= SUMIF (DRV DMaget 1 Magi C) (here you can put it in the cell you want according to your needs. )

The formula roughly means:

For the conditional region D column, sum the qualified cells of the C column according to the cell whose condition is the number 1.

It still looks pretty simple.

PS: in the auxiliary column input, you can enter according to the situation, easy to identify! For example: sales group + color and so on.

Find and define nomenclature recommendation Index: ★

Degree of difficulty: ★★☆☆☆

Suitable for scenarios: in the case of single color or not many colors

Applicable version: all version

The combination of two or more methods is also a very good idea to simplify complexity.

❶ press Ctrl+F to open the find and replace dialog box, click the black triangle button next to format, and click format.

In addition, you can sometimes select the option Select format from cells, but the results of the two methods may not be the same.

For example, some cells are added color and set bold, while some cells do not, which will lead to different statistical results, we can try to explore.

❷ opens the find format dialog box and click on the color below the fill tab.

After clicking, the color is automatically displayed in [Preview], as shown below:

❸ click [find all], select one of the data, and press [Ctrl+A] to select all the colored cells.

Then enter a name in the name box, such as: my name 1.

PS: of course, the name can also be defined as "green" here, and if there are two or more colors, they can be defined as the actual color name + remarks.

❹ writes the formula in E1 cell:

= SUM (my name 1) Sum is the summation function that sums the values in multiple cells represented by the name "my name 1".

So the result comes out!

Recommended index of macro table function method: ★★★★☆

Degree of difficulty: ★★★☆☆

Applicable scene: unlimited color

Applicable version: all version

Macro table functions may be unfamiliar to many people.

What we come into contact with most in the work is the worksheet function, which can be used directly in the cell.

The macro table function must first define a name, and then it can be used in the cell like a worksheet function.

❶ select the D2 cell next to the colored cell, and click [Formula] → [define name] to open the "New name" dialog box.

(or press [Ctrl+F3] to open the name manager, or you can create a new name. )

Enter "my name 2" in the [name] text box, and enter [reference location]:

= GET.CELL (63m Sheet1cm C2)

The formula roughly means: get the value of the fill color of the cell.

(parameter 63 represents the value that returns the fill color of the cell. )

❷ enters the formula in the D2 cell:

= my name 2 and fill down to the last cell D9.

❸ can then use Sumif to sum as we did in our first method.

Of course, you can also put the color next to the formula here. If there are two or more colors, you can use the following formula:

= SUMIF (DJV D my name is 2pm CJV C)

If the color increases or decreases, you can modify the original macro table function:

= GET.CELL (63Chinese Sheet1Christ C2) + NOW () * 0

After modification, if the color changes, add or decrease the color, you can press [F9] to refresh it without having to re-enter the formula.

PS: here you must press [F9] to refresh, otherwise the calculation result may be wrong! Because this macro table function will not refresh automatically!

Some macro table functions can do what the worksheet function cannot do.

On some occasions, friends who don't know how to VBA are worth learning.

VBA programming recommendation index: ★★★☆☆

Degree of difficulty: ★

Applicable scene: unlimited color

Applicable version: all version

This method doesn't work for most people.

Because it involves programming, it is relatively difficult.

However, in most cases, we don't really need to know how to write the code, we just need to know how to use it and how to operate it.

❶ press [Alt+F11] to open the VBA editing interface

Then in the project window on the left, right-click to insert a module, which will generate [Module 1].

❷ copies the code into the code window on the right, and that's fine.

❸ in the worksheet, enter the formula:

The result of color summation C2 C9pm E1 will come out.

The following is the code for everyone to copy and use!

Function color sum rng1 As Range, rng2 As Range Dim r As Range, s As Double 'Please select the range of cells you require! Set rng1 = Intersect (ActiveSheet.UsedRange, rng1) For Each r In rng1 'accumulate if the target cell has the same fill color as the second parameter cell. If r.Interior.Color = rng2.Interior.Color Then s = s + r.Value End If Next color sum = sEnd Function We are using a custom function in VBA here, or we can write a Sub subprocedure, and then attach this subprocedure to a button.

This method, if you have energy and interest, you can learn to record macros, and then make some simple changes, you can complete some automated work, save time and effort.

These are the four main methods of summing by color.

To sum up, in addition to the above four main methods, there may be the following situations in actual work, such as:

Does ❶ add color to interlaced (or interlaced columns) and then sum interlaced (interlaced columns)?

Does ❷ color data above or below a certain value, and then use functions such as Sumif or Sumifs to set order conditions or multi-condition summation?

Does ❸ add colors to a certain department or some people, a certain period of time, etc., and then use the corresponding function to sum?

……

In the work, according to the actual situation, we can find out the rules, analyze and judge them, and make choices.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.