Excel with sumif or other functions? 50

Hope it helps you :)

This problem can be calculated using the sumif function.

Function description: sumif(range,criteria,sum range).

range: represents the range of the condition, which in this case is the range of a2 a7.

criteria: In this problem, the condition starts with a, and can be expressed by a wildcard in the formula, that is, a*

sum range: is the actual sum area, which in this case is the range of b2 b7.

So the number and formula for calculating the model starting with a is.

Result】sumif(a2:a7,"a*", b2:b7), and the result is 338 <>

My idea is to use the wildcard *

Formula Reference ==sumifs(b:b,a:a,"a"&"*"In the same way, if you start with b, change the last a to b.

The sumif function is also available, I am used to using sumifs, which can be modified similarly.

1. Range and sum range are usually different. The former is the conditional region, and the latter is the data (summation) region.

For example, the name of the product in column A, the quantity in column B, and the number of apples:

sumif(a:a,"Apples",b:b)

It can also be the same, but the latter can be omitted at the same time, and there is no need to write it twice.

For example, a1=1, a2=4, a3=3, a4=3, to count the number of column a greater than or equal to 3, sumif(a:a,">=3")

If it must be written.

sumif(a:a,">=3",a:a)

Yes, too, but it's obviously a recipe for it.

2. Examples of texts, numerical values, and expressions.

1. The text is an example of the "apple" above.

2. If it's a number, e.g. =sumif(a:a,3).

To put it simply, both the conditional text and the numeric condition are object-specific.

3. If you want to use comparators + specific data, you need to use expressions, such as the above">=3"。One more column, =sumif(a:a,"Apples", b:b), count the number other than apples.

4. If you want to use a comparator + cell reference, it will be a little more complicated. For example, d1=apples, to calculate the quantity other than apples:=sumif(a:

a,"<>"&d1,b:b)。This point is a little more difficult to understand, there is no reason to talk about it, and that's how it is used.

The same goes for countif.

I don't know, I understand, I don't know You can refer to the function help, there are examples.

The range and sum range must be the same, for example, a1:a100 and b1:b100.

criteria can be:">10","<=10","Second shift","Male","Female"or any number etc.

In the above formula, b:b is used for the cell range of conditional judgment.

c:c is the actual cell that needs to be summed.

a"j is text.

In the formula above, 2 is the number.

In the formula in the figure above">20"It's an expression.

For example, column A Name Column B Salary column A has duplicate names, suppose column A has name A.

We're going to count A's total salary.

sumif(a:a,"a",b:b)

Here a:a is range"a"It's criteria, and b:b is sum range

Find the sum of the data that satisfies the conditions.

The syntax of the sumif function is: =sumif(range,criteria,sum range).

The parameters of the sumif function are as follows:

The first parameter: range is the range of cells, which is used for conditional judgment.

The second parameter: criteria is the summation condition, which is composed of numbers, logical expressions, etc.

The third parameter: sum range is the actual sum range, which needs to be summed by the cells, ranges, or references.

When the third parameter is omitted, the conditional region is the actual summation region.

Here's how to use it:

The summing formula of the function is "sumif(range,criteria,[sum range]"), range" must be used to indicate the range of conditions, criteria is required, and sum range is optional, which means the sum range. Open Excel as shown in the figure and sum the numbers with the value ">15" in cell 4 in the picture.

2.Select a summed cell, click the "Formula" option above the menu bar, and then click "Insert Function".

3.Enter "sumif" in the search box, click "Go", then click the sumif function appears below, and finally click "OK" to complete the insertion of the sumif function.

4.In the figure, in the "function parameters", "range" is a range of cells with a range of 4 and 4 from the above concept, and "criteria" is proposed here as ">15".

5.First fill in "range", place the mouse at the number "11" in the upper left corner, at this time "11" becomes a dotted box, and then still hold down the left button and drag the mouse until the number "24" in the lower right corner, at this time the cells of 4 and 4 have become dotted boxes, and the range is selected at this time.

6.Then enter ">15" in the "criteria" box and click "OK".

7.In this way, the sum of all the numbers greater than 15 out of the 16 numbers is calculated.

The sumif function can be used as follows:

Use the sumif function to sum the values in a range that meet the specified criteria.

The syntax of the sumif function is: sumif(range, criteria, [sum range]).

For example: =sumif(a:a,d2,b:b).

The formula says: Sum column A that meets the condition that column A is equal to D2.

During the use of formulas, please pay attention to the requirements for the use of parameters:

1. Range required. The range of cells used for conditional calculations. The cells in each region must be numbers or names, arrays, or references containing numbers. Null and text values are ignored.

2. criteria required. A condition used to determine which cells are summed, which can be in the form of a number, an expression, a cell reference, text, or a function. For example, a condition can be represented as 32">32"、b5、"32"、"Apples"or today().

3. Important Any textual condition or any condition that contains logical or mathematical symbols must be in double quotation marks (") in brackets. If the condition is a number, you don't need to use double quotes.

Sum Range is optional. The actual cells that require the sum (if you want to sum the cells that are not specified in the range parameter). If you omit the Sum Range parameter, Excel sums the cells specified in the Range parameter (that is, the cells to which the condition applies).

4. Wildcards can be used in the criteria parameter (including the question mark (?).and an asterisk (*The question mark matches any single character; The asterisk matches any string of characters.

If you want to find the actual question mark or asterisk, type a tilde (before the character.)

Oh, the sumif function is here, and the brain is buzzing again.

Find the sum of the data that satisfies the conditions.

=sumif(conditional area, conditional, summing area).

To calculate the sum of grades in column B if column A is a class as "one class". You can use the formula:

sumif(a1:a100,"One shift",b1:b100)

Example=sumif(a:a,"Zhang San",b:b)

Sum all the values of column b for column A for Zhang San.

On a certain day of a certain month, you can add a secondary column month

sumif(range, month to be summed, sum column) "Note: The range header is the month made by the auxiliary column".

sumif（a:b,"1", b) indicates the sum of the values for January, and "1" can be replaced with c1 if it is in c1

=sum (then check the number you want to add) OK.

sumif(conditional judgment area, specified condition, summation region);

Example: It is not clear that you can refer to:

sumif is a commonly used function in Excel, and the usage of the sumif function is to sum several cells, regions, or references according to specified conditions.

The syntax of the sumif function is: =sumif(range,criteria,sum range).range is the conditional region, which is used for conditional judgment. criteria is the sum condition, which is composed of numbers, logical expressions, text, cell content, etc. sum range is the actual sum region, the cell, range, or reference that needs to be summed, and when this parameter is omitted, the conditional range is the actual sum region.

criteria parameter, including a question mark (?and an asterisk (*The question mark matches any single character; The asterisk matches any string of characters. If you're looking for the actual question mark or asterisk, type a sigif (before the character.)

If you have trouble using the Excel function, you can simply press the F1 key and type sumif for help.

The sumif function has three parameters, the first parameter is the conditional region, the second parameter is the condition, and the third parameter is the summation region, and if the summation region is the same as the conditional region, the third parameter can be omitted.

The sum area of the third parameter can be different from the condition area size and shape of the first parameter, but when summing, the program will automatically change to the same (just not showing the changed area), and then sum, and the actual sum cells are determined by the following method: use the cell in the upper left corner of the sum parameter as the starting cell, and then include the cells corresponding to the size and shape of the condition area parameter. Because with a13:

d19 corresponds, and the upper left cell is b13, which corresponds to b13:e19, so:=sumif(a13:

d19,f14,b13) is equivalent to =sumif(a13:d19,f14,b13:e19).

There is no difference between b13 and $b$13 itself, except that the latter has an absolute reference character, and it does not change when it is filled down to the right, and it is still a reference to b13, and if it is not added, the right column mark will change, and the drop-down line number will change, and it is no longer a reference to b13.

Obviously, your formula will not be pulled to the right to fill, only to be pulled down to fill, so as long as the line number is limited, of course, the line number column is also limited, the formula is:

sumif($a$13:$c$19,f14,$b$13:$d$19)

sumif(a$13:c$19,f14,b$13:d$19)

The usage of the sumif function in Excel is to sum several cells, regions, or references based on specified criteria.

The syntax of the sumif function is: =sumif(range,criteria,sum range).

sumif(b2:b19,e2,c2:c19), for the region b2:b19, as long as it is the same as e2.

Add up the corresponding values of c2:c19.

I hope my answer helps.

Find the sum of the data that satisfies the conditions.

In addition, the $ symbol is an absolute referrer, and $ is added to the column number b, and when dragging the filling formula horizontally, the column number will not become equal, leaving column b unchanged. $ is added to the line number 13, and when dragging the fill formula vertically, the line number does not become equal, leaving 13 unchanged. If the formula doesn't need to be dragged to fill, it doesn't matter if you add an absolute reference $ or not.

Look at my diagram and click to adopt.

The row and column labels with $ added remain unchanged when they are populated with a drop-down.

You carefully understand the help of the function and the meaning of the 3 parameters.

sumif helps.