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

Source: Internet
Author: User

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
 
 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.