How to use SUMPROUCT for multi condition summing?

sumproduct((Data region 1 = Condition 1) × (Data region 2 = Condition 2) * (Data region 3 = Condition 3) * Sum region).

The formula is written as above, but be careful:

1."Data region"In general, you need to use an absolute reference so that you can drag 2Pay attention to the parentheses inside, and distinguish the hierarchy ......

If it doesn't work, go to my space and have a look!

The sumifs function in excel can only be used to set a single condition, and the sumifs function is required to set two or more conditions when digging for the same fiber. Here's how:

1. First of all, use Excel software to destroy the kernel, open the data file that needs to be calculated, and use the sum (sum area) function to calculate the total sales.

2. Then enter the condition area, condition, sum area, and complete function according to the sumif function format, and use the formula: "=sumif(f:f,">1100",f:f)”。

3. After completion, you can directly click enter to calculate the result of "greater than or equal to the sum of 1100 sales", <>

4. Then use the formula: "=sumifs(f:f,b:b,b3,c:c,c3)", at this time, sumifs(sum region, conditional region, condition, conditional region, condition. There are two conditions in .

5. Click the Enter key again, and you can calculate the results under the four calculation requirements of this regret, so that the sumifs function is used to complete the sumifs operation under the premise of multiple conditions.

The sum function adds up the values. You can add individual values, cell references, or ranges, or a combination of the three.

For example: sum(a2:a10) adds the values in cell a2:10 together.

sum(a2:a10, c2:c10) adds the values in cell a2:10 and cell c2:c10 together.

The method of summing with the sum function: first, click the cell where you want to display the value of the game code, and enter the [=] sign; Then enter the sum function name sum, select sum, and double-click the mouse. Then manipulate the function arguments in the command line; Finally, click the tick button on the side.

sum method

1. Horizontal summing: Enter the summation function sum and select the data horizontally to perform the horizontal sum, and the demonstration formula = sum(c2:e2).

2. Longitudinal sum: Enter the summing function sum, and the longitudinal selection of data can be used to perform longitudinal mode waiting, and the demonstration formula = sum(d2:d11).

3. Multi-cell summing: Multi-cell summing refers to non-continuous data, which can be entered into the function and select the required cells for summing. Demo formula = sum(c2,d4,e7,c9,e11,e9).

4. Multi-column summation: To sum the uncontinuous multi-columns, after entering the function, select the data one by one. Demo formula = sum(c2:c11,e2:e11).

1. This function cannot directly reference the column, if you must select the entire column, the formula should be as follows.

2. Change the comma to an *.

sumproduct(($b$1:$b$65536="Male")*(c$1:$c$65536="Assistant Manager")*(d$1:

d$65536="Advanced")*(e$1:$e$65536="10")*$f$1:$f$65536)

According to your formula, the evaluation should be the sum of the f columns when these conditions are met at the same time: male, deputy manager, senior, 10

The premise is that there can only be a value in column f, and if there is a word or something, the formula is wrong.

If the data source only has 1000 rows, just select b1:b1000. The same cd e f column must be the same row as b selected, c1000 d1000 ....

Otherwise, the formula is wrong...

=sumproduct(($b:$b="Male")*(c:$c="Assistant Manager")*(d:$d="Advanced")*(e:$e="10")*(f:$f))

Press Ctrl+Shift+Enter at the same time.

sumproduct multi-conditional summation:

sumproduct（（isnumber（find（"2012-3-1"，a1：a3））*b1：b3=1）*（c1：c3））。

sumproduct（（a1：a3=date（2012，3，1））*b1：b3=）*c1：c3））。

If the date is in text format, the formula is: =sumproduct((a1:a3=.)"2012-3-1"）*b1：b3=）*c1：c3））。

sumproduct（（a1：a3="2012-3-1"）*b1：b3="Rabbits"）+b1：b3="Dogs"））c1：c3））。

Excel 2010 Functions and Formulas

Each problem contains one or more solutions, and is integrated with the explanation of one or more knowledge points, which guides the reader to learn the principles in the formula and expand the knowledge while solving the problem. The book also introduces a large number of mature general formulas for bridge answers, which readers can directly apply to practical work. It not only helps to improve the reader's theoretical level and comprehensive practical ability in functions and public sensitivity, but also helps to solve many problems that may arise in the reader's practical work and improve work efficiency.

sumproduct multi-conditional summation:

The sumif function is only suitable for single-condition summarization. If you want multiple conditions, you can use the product and function sumproduct(), such as: =sumproduct((conditional area 1=conditional 1) * (conditional area fanxun domain 2=conditional 2) * (strip shouting car Kai area 3=conditional 3) * summary area) to end the calculation and directly enter.

Use the sum():sum((conditional region 1=conditional 1)*(conditional region 2=conditional 2)*(conditional region 3=conditional 3)*summary region) array formula, do not press enter after entering, and press ctrl+shift+enter to end the calculation.

Excel is a letter that satisfies multiple criteria

If column B >> column A, then column C = column B - column A, if column B A1, B1-A1, B1).

The formula is filled down with sumproduct for sumproduct that satisfies multiple conditions, for example: =sumproduct((a1:a10=condition1) *b1:b10=condition2), c1:c10).

If you find the number of units that meet the conditions, enter =SumProduct((A2:A5>54)*(C2:C5<4)) in D2, and if you sum the number of B columns that meet the conditions, enter =SumProduct(((B2:.)

b5>54)*(c2:c5<4)*b2:b5)

If you don't divide the customer name, just sum the amount of < = 5 million, which is a single condition, and the formula is as follows:

D11 cell input formula=sumproduct((D2:D9<=5000000)*D2:D9)

If you want to sum the amount of each customer name <=5 million, it is a double condition, and the formula is as follows:

E2 cell input formula=if(countif(A$2:A2,A2)=1,SumProduct((A$2:A$9=A2)*(D$2:

d$9<=5000000)*d$2:d$9),"")

Drop-down formula. <>