Excel Formula application: Multi-condition summation, array formula application 2

Http://community.csdn.net/Expert/TopicView1.asp? Id = 4488494 I have made new mistakes recently. I always don't take a closer look at the questions ...... Csdn-Expert Clinic-

Subject:   Question about sum of two conditions Author:   Zhuluzy) Reputation value:   100 Forum:   Use Windows Office Problem points:   100 Replies:   13 Posting time:   2005-12-29 13:51:46

For example, | a B c d _________________________________________ 1 | A1 10 A1 nm 2 | B2 10 C3 nm 3 | C3 10 NF 4 | A1 10 NF 5 | A1 10 NF 6 | B2 10 NF 7 | a1 10 nm Q: the values in column A exist in Column C. In column C (and-And) and column D, which of the following methods must be used for the sum of values in column B equal to nm ?? Both conditions can be summed separately. Condition 1: sum (sumif (A1: A7, C1: C2, B1: B7) condition 2: sumif (D1: D7, = "Nm", B1: B7) But how can we achieve the sum of rows that meet both conditions? The final result is 20.

Reply:Scz123 (chapter)() Credit: 124 2005-12-29 15:06:52 Score: 0       {=SUM(SUMIF(A1:A7,C1:C7&(IF(D1:D7="mn","","a")),B1:B7))} Top     Reply:Zhuluzy)() Credit: 100 2005-12-29 16:01:33 Score: 0       I tried it. This is incorrect, but I am taught this idea. Thank you! The error occurs because C1: C7 & (if (D1: D7 = "MN", "", ", column D is used as a condition range. However, if column A meets condition 1 but has repeated values, whether or not condition 2 is met, the values are summed because column A is duplicate, column C does not have a one-to-one correspondence with column A (column C is not repeated), so writing is incorrect. Top     Reply:Scz123 (chapter)() Credit: 124 2005-12-29 16:03:46 Score: 0       Oh, the poster is nm. Do not make a mistake {= sum (sumif (A1: A7, C1: C7 & (if (D1: D7 = "Nm ","", "A"), B1: B7 ))} Top     Reply:Scz123 (chapter)() Credit: 124 2005-12-29 16:09:44 Score: 0       Oh, understanding is incorrect. Think about it again. Top     Reply:Zhuluzy)() Credit: 100 2005-12-29 16:37:58 Score: 0       I used the following idea (using the empty column F), which borrowed the sub-chapter idea. I think it is OK, but it still seems to be unsuccessful !!!! Depressed ...... = sum (D1: D7 = "Nm") * (A1: A7 = C1: C7 & if (D1: D7 = "Nm", A1, "") * D2: d11) Top     Reply:Scz123 (chapter)() Credit: 124 2005-12-29 16:38:08 Score: 0       {= Sum (if (D1: D7 = "Nm", if (not (ISNA (MATCH (C1: C7, A1: A7, 0), B1: B7, 0), 0)} It's a bit complicated. If you have any questions, send me a message and continue tomorrow. Top     Reply:Scz123 (chapter)() Credit: 124 2005-12-29 16:43:39 Score: 0       {= Sum (D1: D7 = "Nm") * Not (ISNA (MATCH (C1: C7, A1: A7, 0) * (B1: B7 ))} this works too. Top     Reply:Yiqun_zhao (lanfan Yu Xuan mail: yiqun_zhao@163.com)() Credit: 133 2005-12-30 08:11:00 Score: 0       This is simple: Enter the following formula in the edit column: = sum (if ($ A $1: $ a $7 = $ C $1) * ($ d $1: $ d $7 = "Nm"), $ B $1: $ B $7) at the same time, press Ctrl + Shift + enter to complete the formula, the formula in the editing column is represented as: {= sum (if ($ A $1: $ a $7 = $ C $1) * ($ d $1: $ d $7 = "Nm"), $ B $1: $ B $7)} result: 20 Top     Reply:Yiqun_zhao (lanfan Yu Xuan mail: yiqun_zhao@163.com)() Credit: 133 2005-12-30 08:17:00 Score: 0       Tip: scz123 (Chapter): Your formula is incorrect for the following reasons: although you can get the correct answer 20 for the formula, after you get the answer, change the NF in column D of the corresponding row of column A to nm. The answer to your formula is still 20 (instead of 30), so your formula is faulty! Reference formula: {= sum (if ($ A $1: $ a $7 = $ C $1) * ($ d $1: $ d $7 = "Nm"), $ B $1: $ B $7 ))} Top     Reply:Scz123 (chapter)() Credit: 124 2005-12-30 08:33:00 Score: 0       No, right? Yiqun_zhao means to change D4 to nm, but the C4 value is empty. The total valid rows are only two (rows 1 and 2), so the result is 20, change the NF in column D of the corresponding row of column A as follows to nm, the formula still produces 20 because column C only has two valid values {= sum (D1: D7 = "Nm") * Not (ISNA (MATCH (C1: C7, a1: A7, 0) * (B1: B7)} D1: D7 = "Nm": D column must be equal to nmnot (ISNA (MATCH (C1: C7, A1: a7, 0): column C values must exist in column A. The formula {= sum (if ($ A $1: $ a $7 = $ C $1) * ($ d $1: $ d $7 = "Nm"), $ B $1: $ B $7 ))} it should only conform to the sum of column A equal to A1 ($ C $1) and column D equal to nm, corresponding to rows 1 and 7, which may be different from the meaning of the landlord. Top     Reply:Yiqun_zhao (lanfan Yu Xuan mail: yiqun_zhao@163.com)() Credit: 133 2005-12-30 11:01:00 Score: 0       Scz123 (Chapter): for example, if the value in column A exists in Column C, it indicates that the value in column A exists in Column C, the value in Column C does not exist in column. That is to say, column C only plays a role in filtering column A. It does not matter whether column C has null values. Haha. If you want the value in Column C to exist in column A, follow your ideas! Top     Reply:Scz123 (chapter)() Credit: 124 2005-12-30 12:50:00 Score: 0       Oh, I was wrong. Ah, I made a mistake again. I always did not read the questions carefully. Top     Reply:Scz123 (chapter)() Credit: 124 2005-12-30 13:04:00 Score: 0       Then {= sum (D1: D7 = "Nm") * Not (ISNA (MATCH (A1: A7, C1: C2, 0 ))) * (B1: B7)} formula {= sum (if ($ A $1: $ a $7 = $ C $1) * ($ d $1: $ d $7 = "Nm"), $ B $1: $ B $7)} other values in Column C have not been taken into account. Top