EXCEL 多條件求和

 

This tip provides a number of examples that should solve most of your counting and summing problems.
The example formulas presented in this tip use the simple database table shown below. You will need to adjust the formulas to        account for your own data.
1.Sum of Sales, where Month="Jan"--求一月銷售額之和
This is a straightforward use of the SUMIF function (it uses a single criterion):
  =SUMIF(A2:A10,"Jan",C2:C10)
2.Sum of Sales, where Month<>"Jan"--求一月之外月份的銷售額之和
Another simple use of SUMIF (single criterion):
  =SUMIF(A2:A10,"<>Jan",C2:C10)
3.Sum of Sales where Month="Jan" or "Feb"--求一月或二月銷售額之和（公式中的加號(hào)表示“或”的意思）
For multiple OR criteria in the same field, use multiple SUMIF functions:
  =SUMIF(A2:A10,"Jan",C2:C10)+SUMIF(A2:A10,"Feb",C2:C10)
4.Sum of Sales where Month="Jan" AND Region="North"--求一月并且地區(qū)為北方的銷售額之和
For multiple criteria in different fields, the SUMIF function doesn't work. However, you can use an array formula. When you enter      this formula, use Ctrl+Shift+Enter:
  =SUM((A2:A10="Jan")*(B2:B10="North")*C2:C10)(數(shù)組公式,輸入公式后按ctrl+shift+enter)
5.Sum of Sales where Month="Jan" AND Region<>"North"--求一月并且北方之外地區(qū)的銷售額之和
Requires an array formula similar to the previous formula. When you enter this formula, use Ctrl+Shift+Enter:
  =SUM((A2:A10="Jan")*(B2:B10<>"North")*C2:C10)(數(shù)組公式)
6.Count of Sales where Month="Jan" AND Region="North"--計(jì)算一月并且地區(qū)為北方的數(shù)量For multiple criteria in different fields, the COUNTIF function doesn't work.  you can use an array formula.
  =SUM((A2:A10="Jan")*(B2:B10="North"))(數(shù)組公式)
7.Sum of Sales where Month="Jan" AND Sales>= 200--求一月份銷售額超過200的銷售額之和
Requires an array formula similar to the previous example. When you enter this formula, use Ctrl+Shift+Enter:
  =SUM((A2:A10="Jan")*(C2:C10>=200)*(C2:C10))(數(shù)組公式)
8.Sum of Sales between 300 and 400--求銷售額在300和400之間的銷售額之和
This also requires an array formula. When you enter this formula, use Ctrl+Shift+Enter:
  =SUM((C2:C10>=300)*(C2:C10<=400)*(C2:C10))(數(shù)組公式)
9.Count of Sales between 300 and 400--計(jì)算銷售額在300至400之間的數(shù)量
This also requires an array formula. When you enter this formula, use Ctrl+Shift+Enter:
  =SUM((C2:C10>=300)*(C2:C10<=400))(數(shù)組公式)