Sum range of cells based on the rule in Excel
Adds the cells specified by a given criteria


Sum range of cells based on the rule.


The Excel SUMIF function returns a sum of number based on given criteria.
For example, suppose that a list of digits is in a column and user want to add figures that are more than 5, the formula can be =SUMIF({2;8;10;3;5},”>5″) add numbers that are more than 5 and returns 18.


=SUMIF(range, criteria, [sum_range])


range – The range of cells to evaluate the criteria
criteria – The criteria to apply for addition to a range
sum_range [optional] – The reference cell from which to sum values. If sum_range is omitted, the cells in range are added together instead.

Return Value

Sum of values based on the criterion

Key Notes

  • The SUMIF function calculates the sum from a range of value determined as per the given rules.
  • For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation marks (“) like “<30”.
  • The user can find a literal question mark or asterisk by using a tilde (~) symbol before a question mark or asterisk (i.e., ~?, ~*). A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.


For example, suppose that two specification (Item Type, Inventory) is in two different column and user want to add an inventory of each Item Type the formula can be following: like for  ButterCream, 

=SUMIF($E$5:$E$13,H8,$F$5:$F$13) it return 21 as (8+5+8).


Keep Reading Similar Functions:

To add multiple numbers in Excel


The SUM function returns a number after adding multiple numeric arguments together =SUM(5,10,15) the Excel add numbers together and returns 30.

Read More »
Sum range of cells based on multiple criteria in Excel


The SUMIFS function returns an addition of numerical as per multiple criteria based on the match of dates, numbers, and text against the reference.

Read More »
Multiply the corresponding value and sum in Excel


The SUMPRODUCT function returns a sum of the product of numbers together distributed in multiplies ranges or arrays, an easier way to add multiple integers.

Read More »