SUMIFS

Description

Use this function to return the sum of cells that meet a set of multiple conditions.

SUMIF(range, criteria_range_1, criteria_1, […, [criteria_range_127, criteria_127])

This function accepts the following arguments:

名稱	必要	說明	Valid input
`範圍`	是	The range of cells to be summed.	A cell, a cell range, or a formula which results in either of these.
`criteria_range_1`	是	The range to be evaluated using `criteria_1`. `criteria_range_1` and `criteria_1` set up a search pair where the range is searched for the specified criteria. Once items in the range are found, their corresponding values in `range` are added.	A cell, a cell range, or a formula which results in either of these.
`criteria_1`	是	The criteria that defines which cells in `criteria_range_1` will be added.	A number, a cell, a cell range, or a formula which results in any of these.
`criteria_range_n`	沒有	Additional range(s). Up to 127 additional `criteria_range` values can be included.	A cell, a cell range, or a formula which results in either of these.
`criteria_n`	沒有	Criteria for evaluating the associated range. Up to 127 additional `criteria` values can be included.	A number, a cell, a cell range, or a formula which results in any of these.

A	B	C	暗示性的對話 (D)	E
日期	色彩	狀態	數量	值
2-Jan	Red	TX	4	$23.75
18-Feb	Blue	OH	3	$27.80
28-Feb	Blue	NJ	2	$31.20
15-Mar	綠色	TX	3	$45.23
13-Apr	Red	CO	4	$58.60
30-Apr	Red	IL	2	$55.10
20-May	Red	AZ	1	$48.90
14-Jun	綠色	WA	2	$52.30
22-Jul	Red	NY	4	$62.75
11-Aug	綠色	TX	4	$41.65
19-Aug	綠色	OR	1	$43.70
3-Sep	綠色	FL	1	$33.50
6-Oct	綠色	MA	3	$39.15
25-Oct	Blue	GA	1	$29.99
7-Nov	Blue	WA	2	$18.99
9-Dec	Red	TX	3	$37.42

公式	說明	結果
`=SUMIFS(E2:E17, B2:B17, "red")`	Sums all the values in column E (Value) where the value in column B (Color) is "red".	$286.52
`=SUMIFS(E2:E17, B2:B17, "red", C2:C17, "TX")`	Sums all the values in column E (Value) where the value in column B (Color) is "red", and the value in column C (State) is "TX".	$61.71
`=SUMIFS(E2:E16, D2:D16, ">3")`	Sums all the values in column E (Value) where the value in column D (Color) is greater than 3.	$186.75
`=SUMIFS(E2:E17, B2:B10, "blue")`	Sums all the values in column E (Value) where the value in column B (Color) between rows 1 and 10 is "blue". This throws an error because the ranges are not the same size.	#VALUE!

The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria should include logical operators (>,<,<>,<=,>=) as needed.
Criteria can be applied to dates, numbers, and text.
Each individual condition must be defined by its own range-criteria pair.
SUMIFS supports up to 127 range-criteria pairs.
The SUMIFS function requires actual ranges for all range arguments.
All ranges must be the same size or SUMIFS will return a #VALUE! error.
SUMIFS can calculate sums based on cells that are blank ("") or not blank ("<>").
Conditions in SUMIFS are joined by AND logic. To be included in the final result, all conditions must evaluate to be TRUE.
If text is included by a cell or range reference, then it is treated as a zero. If text is included without quotation marks, the function returns a #NAME! error. If text is included with quotation marks, the function returns a #VALUE! error.
If arguments contain errors, SUM will return an error.
SUMIFS isn't case-sensitive.
SUMIFS supports wildcards.

TBD

ABS
EXP
LN
MOD
N
POWER
PRODUCT
SIGN
SQRT
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT