In an excel cell, find multiple numbers that meet the conditions and sum them

Step 1: Classify according to the tower type, Step 2: Sum with the sumproduct function according to the tower type, <>

The formula can be copied and pasted to be used directly, specifically: =SumProduct(($b$4:$b$9=g4)*(d$4:$d$9<=20)*$c$4:$c$9)

If in doubt, you can continue to communicate!

There are sumifs in the 2007 version and above, which can be summed with multiple conditions.

The 03 version of sumif cannot be summed by multiple conditions, you can use a pivot table or filter by 3 conditions and then use the subtotal function.

The formula is very simple, and it can be solved with a sumif function.

Appointment: SUMI($a$5: $ah$8,AI5,$b$5)Qualifying: SUMI($a$5:$ah$8,AI5,$c$5)<>

Your screenshot doesn't have a row and column number.,Change it yourself.。

If you do this, it's more convenient to count.

AK3 formula: =sumif($a$2:$ah$2,"Number of people to take the test",a3:ah3)

al3 formula: =sumif($a$2:$ah$2,"Number of Qualified Persons",a3:ah3)

am3 formula: =ak3 al3

The font size is too small to read, but it should be possible to use the formula.

With the sumif function, the judgment value is the student number.

sumif function.

The sumif function is used to calculate the sum of all numbers in a cell range or array that meet a certain specified condition.

sumif function format.

sumif(range,criteria,[sum range]). range (required): indicates the range of cells to be determined.

criteria (required): Indicates the criteria to be judged, which can be in the form of numbers, text, or expressions. For example, 16,"16"、"16"、"Books"or">a1。

Sum Range (optional): Indicates the range of cells to be calculated based on the result of the condition. If the parameter is omitted, the cells that meet the criteria in the range of cells specified by the parameter range are summed.

sumif(b2:b14,"Engineering Department",d2:d14)<>

The sumifs function can quickly sum multi-conditional cells.

sumifs function syntax:

sumifs(sum region, conditional region 1, conditional region 1, conditional region 2, conditional region 2, conditional region n, conditional n).

The first parameter is the summation region, and the following condition region corresponds to the conditions one by one, which is illustrated by one case below.

For example, the following diagram shows:

Calculate the total score of Xiao Ming's three subjects.

sumifs(c92:c100,a92:a100,e92)e92 is the output area, blue is the summation area, red is the conditional area, and Xiao Ming in the green box is the condition.

1. The data in the figure below sums the data with a female operation volume greater than 80.

2. This is a multi-condition summation, which we can achieve with the implicit noise to sumifs function. Enter the formula in cell d2: =sumifs(c2:c11,b2:b11,"Female",c2:c11,">80")

3. Press the enter key to get the result 301. From the formula =sumifs(C2:C11,B2:

b11,"Female",c2:c11,">80"), you can see that the sumifs function writes the formula rule: =sumifs(sum region, first conditional region, first condition, second conditional region, second conditional front carrying foci).

4. Then, from this rule, we can cope with the sum of more than 2 conditions, =sumifs(d2:d11,b2:b11,"Female",c2:

c11,">30",d2:d11,">80"Press enter to get the result. Excel can sum the values that meet both conditions.

Find the total sales of "Division 1" and "Male Sibling": =sumifs($e$2:$e$9,$c$2:$c$9,g2,$d$2:$d$9,h2), press Enter to end.

That is the summing of multiple conditions, the common functions sumifs, sumproduct, and the array formulas of sum and if.

For example, in April, the sum of the sales of commodity A and a single transaction greater than 40,000 yuan.

The specific formula is:

sumifs(c:c,a:a,">2021/3/31",a:a,"<2021/4/30",b:b,"a",c:c,">4")

sumproduct((month($a$2:$a$18)=4)*(b$2:$b$18="a")*c$2:$c$18>4),$c$2:$c$18)

The final array formula is as follows, you need to press the Ctrl Shift Enter key at the same time.

sum(if((month($a$2:$a$18)=4)*(b$2:$b$18="a")*c$2:$c$18>4),$c$2:$c$18,0))

Method steps.

As shown in the following diagram, find out the information terms that meet both "Condition 1 is greater than 500" and "Condition 2=a", and the results are displayed in column C.

Please click Enter a description.

Select the C2 cell, enter "=" in the cell, and click the [Insert Function] button.

Please click Enter a description.

As shown in the figure below, the Insert Function dialog box pops up. Select the "if" function in the "Select Function" and click the OK button.

Please click on the Brigade to enter **Description.

The [Function Parameters] dialog box is displayed:

Move the cursor after the first parameter and enter "and(a2>500,b2=) in the dialog box"a")”

After the second parameter, enter "Condition met";

Enter "" after the third parameter, and click the [OK] button after setting it.

Please click Enter a description.

Back to the worksheet, you can see that the first record satisfies the condition, which is displayed as "Condition Met" in cell C2.

Please click Enter a description.

Select cell C2 and drag the left mouse button to find out whether other pieces of information meet the conditions, and the results are displayed in column C.

Please click on the hidden judgment to enter **Description.