您现在的位置是:首页 > 办公软件 > excleexcle
EXCEL 多条件求和
爱读书aread.cc2022-10-03 23:30:08excle0人已围观
简介.爱读书aread.cc翻译,转载请保留此信息 . 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 dat
.爱读书aread.cc翻译,转载请保留此信息 .
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"--求一月或二月销售额之和(公式中的加号表示“或”的意思)
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"--求一月并且地区为北方的销售额之和
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)(数组公式,输入公式后按ctrl+shift+enter)
5.Sum of Sales where Month="Jan" AND Region<>"North"--求一月并且北方之外地区的销售额之和
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)(数组公式)
6.Count of Sales where Month="Jan" AND Region="North"--计算一月并且地区为北方的数量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"))(数组公式)
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))(数组公式)
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))(数组公式)
9.Count 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))(数组公式)
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"--求一月或二月销售额之和(公式中的加号表示“或”的意思)
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"--求一月并且地区为北方的销售额之和
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)(数组公式,输入公式后按ctrl+shift+enter)
5.Sum of Sales where Month="Jan" AND Region<>"North"--求一月并且北方之外地区的销售额之和
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)(数组公式)
6.Count of Sales where Month="Jan" AND Region="North"--计算一月并且地区为北方的数量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"))(数组公式)
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))(数组公式)
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))(数组公式)
9.Count 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))(数组公式)
Tags:
很赞哦! ()
相关文章
随机图文
-
EXCEL 改变文本型日期类型
爱读书aread.cc翻译,转载请保留此信息 . To change the formatting from Text to Date, use Text to Columns technique:(text to columns--中文版中的“分列") 1. Select the column formatted as text. 2. From the -
EXCEL 寻找指定区域最大值的地址
爱读书aread.cc翻译,转载请保留此信息 .如图,寻找A1:A10中最大数值的地址。我把这个公式介绍给大家,是因为其中包括了MAX,INDEX,MATCH,CELL等公式的使用。这几个公式在实际工作中都很常用,特别是中间的两个公式。建议 -
EXCEL 快速求和
爱读书aread.cc翻译,转载请保留此信息 . 1. Select the range of cells along with an empty cell in a vertical or horizontal direction. 2. Press "ALT+ ="OR Click the AutoSum icon(∑). The SUM formula is ad -
excel插入剪贴画_excel插入图片
Excel-插入图片/剪贴画/形状 平时工作中制作表格时,我们常常会插入一些图片、剪贴画、图
留言与评论 (共有 条评论) |