SUMIF function. Sum the values of cells matching the criteria.(Microsoft Excel)


Japanese version

Extended version of the SUM function to obtain the sum of cells matching a criteria.

The SUMIF function specifies a single condition, but there is also a SUMIFS function that specifies multiple criterias.

How it works

=SUMIF(range, criteria, sum_range)
rangeRequired argument. 
Range of cells to be evaluated for the search criteria.
criteriaRequired argument. Criteria to be totaled.
sum_rangeSame as range.Cell range to be summed.
Specify when the condition and the cell range of the tally are different.
Non-numeric cells are excluded.


For example, it can be used to total the sales of a specific category of products.
It is also used in cases where a comparison of large and small amounts is required, such as when the total is greater than a certain amount.

The following is an example of a SUMIF function that refers to the table below.

Example of summing numbers matching a specific text.

The following is an example of searching List for Price matching Type in column C and summing them.

The range and sum_range are fixed as absolute cell reference, since the referenced list does not change even if the position is changed by copying.


Ambiguous search. Example of using wildcards.

The wildcard * can be used for fuzzy searches.


The "*" can be added only to the front for backward matching, only to the back for forward matching, or to both for partial matching.

Example of setting a numerical threshold and summing.

If a comparison operator is specified for a criteria, it becomes a threshold value.

Total Amount of Amount over 400 and Amount over 1000.

The operators are combined as a string.



If a cell range is specified for the "Criteria", it will be Spill.



Microsoft Excel Functions Math & Trig