How to use the SUMIF Function


You operate the SUMIF function to sum the values in a range that meet criteria that you specify. SUMIF is the function used to sum the values according to a single criterion. Using this function, you can locate the sum of numbers applying a condition within a range. It comes beneath Math & Trigonometry capabilities. Just like the name, this will sum if the criteria given is satisfied. This function is used to locate the sum of particular numbers within a massive statistics set.

SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values whilst adjoining cells meet standards primarily based on dates, numbers, and text.

Again, SUMIF is not case-sensitive.


  • The number one distinction between the 2 Excel functions is SUMIF only supports a single condition. If you need to apply multiple criteria, use the SUMIFS function. When using SUMIF, we are able to examine most effective one condition, whereas different criteria can be evaluated under SUMIFS formula.
  • SUMIFS is available from MS Excel 2007.
  • The function can be most effectively used for adding a single continuous range based on a single specified range with a single criterion, whereas, SUMIFS can be implemented over multiple continuous ranges.

What is the purpose of SUMIF function in MS Excel?

The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be implemented to dates, numbers, and textual content. This function supports logical operators (>, <, <>, =) and wildcards (*,?) for partial matching. The sum of values supplied.


=SUMIF (range, criteria,[sum_range]

Parameters or Arguments


The range of cells that you want to apply the criteria against.


The criteria used to determine which cells to add.


Optional. It is the range of cells to sum together. If this parameter is overlooked, it uses range as the sum_range.

Example 1: Using SUMIF

Function: =SUMIF(B2:B6,100)
Result: $200
Explanation: This function, residing in C3 on the worksheet below, sums the values in cell range B2:B6 that equal 100. The default operator is equals and is not written. The range for applying the criteria is B2:B6 and it is also the range-to-sum as one is not specified.

Getting the Most out of the Excel SUMIF Function

As a financial analyst, SUMIF is a regularly used function. Carry out financial forecasting, reporting, and operational metrics tracking, examine financial data, create financial models and many more.

Assume we are given a table listing the consignments of vegetables from different suppliers. The names of the vegetable, names of suppliers, and quantity are in column A, column B, and column C, respectively. In this scenario, we can use the SUMIF function to find out the sum of the amount related to a particular vegetable from a particular supplier.

  • The function will make the sum of values in accordance to criteria given.
  • The  function is well matched with numeric, date, text data
  • Conditions can express from the result of other functions or operators
  • The  function can be may be carried only for a single column at a time
  • A unique condition is allowed to test with SUMIF function
  • Value will be an error result when the range does not match with criteria range
  • Part of the word or sentence will be matched using a wildcard. Asterisks symbol use with a series of characters and question mark for a single character.
  • While using text along with numeric values, the criteria should enclose within a double quotation, but not if it consists only of a numeric value.

You can also read our article on How to learn Excel in 8 Simple Steps


Leave a comment

Your email address will not be published.