Excel cells contain character summation

Sum the numeric values of cells containing the same characters in **" is as follows:

1. Open the excel sheet;

2. As can be seen from the inscription, the sum of column B containing the conditions of column D in column A can be achieved by using wildcards on the basis of the sumif conditional summation function, or by isnumber+find in the sumproduct function;

sumif function.

Enter the following formula in cell E2 and fill the formula down.

sumif(a:a,d2&"*",b:b)

The formula means: sum the corresponding column B of the conditions starting with cell D2 in column A.

sumproduct+isnumber+find function.

Enter the following formula and fill it down.

sumproduct(isnumber(find(d2,a$2:a$10))*b$2:b$10)

The formula means that the corresponding column B data that satisfies the condition that D2 exists in the A2:A10 cell is summed.

4. Note: sumproduct does not support the wildcard character *, but the included relationship can be represented by isnumber+find.

microsoft

Excel can carry out various data processing, statistical analysis and auxiliary decision-making operations, etc., with functions such as opening or closing header rows, adjusting each row with a single formula for calculated columns, data sorting and filtering, custom formulas and text input. Currently, Microsoft

Excel is widely used in many fields such as management, statistics, finance, and so on.

There are 11 types of commonly used functions in Excel, including database functions, date and time functions, engineering functions, financial functions, information functions, logical functions, query and reference functions, mathematical and trigonometric functions, statistical functions, text functions and user-defined functions. Users can easily solve a variety of problems according to these functions.

1. First of all, open the excel** (as shown in the figure below) that needs to sum the data, there are words and numbers in the cell, and it is impossible to directly summarize and sum the data in the cell.

Please click Enter a description.

2. Then, we enter the first number next to the first cell, <> please click to enter **Description.

3. Continue to select a cell below, press and hold: Ctrl+E, this is a natural fill shortcut key, and the corresponding data below will be automatically extracted.

Please click Enter a description.

4. In this way, you can directly summarize: summing, isn't it very simple and convenient?

Please click Enter a description.

1. First, take a look at the raw data. Column A is the name of the company, and we need to determine whether the text of column A contains the word "Beijing" in column B.

2. Double-click B2 and enter the formula: =find("Beijing", A2)B2 cell returns 1, which means that the position of the word "Beijing" in the text of A2 starts from the first character.

3. Then use the if function to convert true to "y", and false to empty display. The formula is =if(isnumber(find("Beijing",a2)),y","Excel can sum the corresponding values.

Formula: sum(b2:j2).

The sum formula has the function of automatically ignoring the text, and the result meets your requirements.

I opened a table and entered the values for verification, please see the figure:

If your formula is not like this, only by posting a similar picture can it be possible to help find the reason, directly I say yes, you say no, there is no follow-up.

Non-standard data calculations are more complicated, but upstairs, try to solve the problem ,..

sumproduct(iferror(replace(b2:h2,iferror(find("（b2:h2),99),99,""1,0))

Then press Ctrl+Shift+Enter to end the array function to calculate the calculation with () after it.

B2:H2 is the data area, which is modified according to the actual situation.

Building master data 58 (steam) + 36 (movie) The above formula cannot be recognized and calculated, and it is no problem to implement, but it will be very complicated, and the formula is inelastic, and there are other formats that cannot be automatically adapted.

The data needs to be adjusted to 84 (steam58 + movie 36) so that it can be calculated.

Why don't you write a separate column for the comments?

It shouldn't have been annotated like that in the first place.

Right-click - Insert annotation Doesn't it smell good.

Sum the numeric values of cells containing the same characters in **" is as follows:

1. Open the excel sheet;

2. As can be seen from the inscription, the sum of column B containing the conditions of column D in column A can be achieved by using wildcards on the basis of the sumif conditional summation function, or by isnumber+find in the sumproduct function;

3 (1) sumif function.

Enter the following formula in cell E2 and fill the formula down.

sumif(a:a,d2&"*",b:b)

The formula means: sum the corresponding column B of the conditions starting with cell D2 in column A.

3 (2) sumproduct+isnumber+find function.

Enter the following formula and fill it down.

sumproduct(isnumber(find(d2,a$2:a$10))*b$2:b$10)

The formula means that the corresponding column B data that satisfies the condition that D2 exists in the A2:A10 cell is summed.

4. Note: sumproduct does not support the wildcard character *, but the included relationship can be represented by isnumber+find.

Microsoft Excel can carry out various data processing, statistical analysis and auxiliary decision-making operations, etc., with functions such as opening or closing header rows, adjusting each row with a single formula for calculated columns, data sorting and filtering, custom formulas and text input. At present, Microsoft Excel is widely used in many fields such as management, statistics, finance, and finance.

There are 11 types of commonly used functions in Excel, including database functions, date and time functions, engineering functions, financial functions, information functions, logical functions, query and reference functions, mathematical and trigonometric functions, statistical functions, text functions and user-defined functions. Users can easily solve a variety of problems according to these functions.

A1 cell formula, copy the formula below. The sum of b5 is equal to 32

lookup(10^9,--mid(a1,lookup(10^9,find(row($a$1:$a$10)-1,a1)),row($a$1:$a$10)))

You can change the address of A1 according to your **.

There is a text that cannot be summed, only the number is count(), and a few cells are counted as many, and the number in the text cannot be recognized.