This article includes formulae and examples to explain how to use SUMIF in Google Sheets. For instance, you get a Google Sheets spreadsheet with a database of bills. You only like to add up the costs that exceed a specific threshold. Alternatively, you may have a listing of order items and their associated quantities. And all you want to see is the full order value of a particular product. The SUMIF method comes in handy in this situation.

SUMIF adds inputs depending on the numeric, word, time, and arbitrary conditions. SUMIF and SUMIFS are two main functions in Google Sheets that total data depending on conditions. The SUMIF function adds integers together depending on a single condition. SUMIFS, on either hand, adds figures depending on several criteria.

Syntax and Arguments for the SUMIF Function in Google Sheets

SUMIF is a function that combines the SUM and IF functions. For a particular criterion, the IF operator examines the list of data. The SUM function then adds up the figures in the columns that satisfy the criterion.

The SUMIF tool in Google Sheets has the following syntax:

=SUMIF(range, criteria, [sum_range])

Here are the Arguments belong to the SUMIF Function:

• range – The cell range in which we search for cells that satisfy the requirements.
• criterion – The criteria used to decide which cells should be included in the spreadsheet. The criterion can be based on a number, a word phrase, a period, a cell reference, an equation, a boolean operator, or any other purpose.
• sum_range – This is an optional parameter. If the associated range element fits the criterion, it is the data range with values to sum. If you leave this argument out, the ‘range’ will be summed automatically.

Let’s look at how to utilize the SUMIF function to add numbers based on certain criteria.

With Number Criteria – SUMIF Function

Making use of the below-mentioned operators, you may aggregate values in a range of cells that fulfill specific requirements.

• greater than (>)
• less than (<)
• greater than or equal to (>=)
• less than or equal to (<=)
• equal to (=)
• not equal to (<>)

Assume you have the spreadsheet and you’re looking for total sales of 1000 or more.

You may use the SUMIF function in the following way:

To begin, choose the cell in which you wish the sum’s result to display (D3). Enter this equation and click ‘Enter’ to add integers in B2:B12 that are approximately equal to 1000:

• =SUMIF(B2:B12,”>=1000″,B2:B12)

The range and sum range parameters (B2:B12) are the same in this sample equation. We also put the number before the comparison operator, enclosing it with quotation marks. Numbers higher than or equal to 1000 were searched for using the formula. The result was shown in cell D3 after adding together all the matching values.

Because the range and sum range parameters are the same, you may get the same result by using the following syntax:

• =SUMIF(B2:B12,”>=1000″)

Alternatively, rather than using the integer condition, you may use the cell value (D2) that includes the number. Then, in the criterion argument, combine the comparison function with the cell reference:

• =SUMIF(B2:B12,”>=”&D2)

The comparison function is still enclosed in double quote marks. An ampersand  (&) joins the operator and the cell reference. You also don’t have to put quotation marks around the cell reference.

To build criteria in the criterion parameter, you may use additional logical operators in the same manner. To total integers less than 500, for instance –: =SUMIF(B2:B12,”<500″)

Word Conditions and the SUMIF Function

If you wish to sum up the numbers in a cell range (column or row) that match to cells with specified content. Simply stated, in the criterion argument of your SUMIF formula, add that text or the cell that includes the information. Keep in mind that word strings must be wrapped in double-quotes (” “).

If you wanted to know the total quantity of sales in the ‘West’ area, for instance, you might apply the formula below:

• =SUMIF(C2:C13,”West”,B2:B13)

The SUMIF function in this equation looks for the word ‘West’ in cell range C2:C13 and puts up the sales amount in line B. The outcome is then shown in cell E3.

Rather than use the word in the criterion argument, you may refer to the cell that includes the text:

• =SUMIF(C2:C12,E2,B2:B12)

Let’s now compute the overall profit of all areas excluding the West. To do this, we’ll utilize the not equal to expression (>) in the method:

• =SUMIF(C2:C12,”<>”&E2,B2:B12)

SUMIF with WildCards

The SUMIF function with textual criteria verifies the range against the precise provided text in the manner above. The numbers are then added together to produce precise text. All other numbers, even partially matched text strings, are ignored. To add the numbers using partial text data that match. In your criterion, you must include one of the preceding wildcard characters:

• (Question mark) is used to identify any specific character in the given text, regardless of where it appears.
• (Asterisk) is used to discover terms that are similar to a series of letters.
• (Tilde) can be used to match the content that contains a question mark (?) or an asterisk (*).

Date Criteria and the SUMIF Function

You may also use the SUMIF function to selectively total items depending on timestamp conditions. For instance, numerals that match to a specific date, or that are before or after that date.

The time must be provided using a date function recognized by Google Sheets, like DATE() or TODAY ().

Assume you wish to include the sales quantities that occurred on or before (=) November 29, 2019. You may use the SUMIF function to sum those sales data in one of the following ways:

• =SUMIF(C2:C13,”<=November 29, 2019″,B2:B13)

Every cell from C2 through C13 is evaluated using the above-mentioned algorithm. But only those cells with dates on or before November 29, 2019 (29/11/2019) are matched. The gross amount relating to those matching columns from the cell range B2:B13 is then added together. The outcome is then shown in cell E3.

In the criterion section of the equation, you can also utilize cell value rather than date:

• =SUMIF(C2:C13,”<=”&E2,B2:B13)

If you wish to add the sales totals depending on the current date. In the criterion parameter, you could implement the TODAY() function.

So, these are the some methods you need to know about the SUMIF function and how to use it.