How to use Sum If in Google Sheets?

Google Sheets is an essential tool for businesses and individuals alike, allowing them to organise data, collaborate with others and gain insights into their information. One of the most useful functions in Google Sheets is the SUMIF formula. This function allows you to quickly and easily sum data based on specific criteria, saving you time and effort.

In this article, we have discussed everything you need to know about how to use SUMIF in Google Sheets in a few simple steps.

Also read: How to create a table in Google Sheets?

How to use SUMIF?

The basic syntax of the SUMIF function is =SUMIF(range, criterion, [sum_range]).

Range

This is the range of the cells that you want to evaluate for your criteria.

Criterion

This is the specific criteria that you want to use to identify the cells to be summed.

Sum_range

This is the range of cells that you want to add up. This is optional; if you omit it, the SUMIF function will simply add up the cells specified by the range argument.

Examples of SUMIF with different criterion

Below we have explained the use of SUMIF with different criterion by using some examples.

SUMIF with Text criteria
SUMIF with Numbers
SUMIF with Date
SUMIF with logical operators
SUMIF with Wildcard Characters
SUMIF with Blank and Not blank cells

SUMIF with Text criteria

In this example, we are summing up the number of files completed by Castello. To use a text as your criterion, you must use the Quotation mark(“”) to enclose the criterion.

Step Choose the cell where you want to see the results and type the formula =SUMIF(A2:A8,”Castello”,B2:B8) and press Enter.

In this example, we have used cells A2 to A8 as the range and Castello as the criterion and B2 to B8 as the sum_range. It will check from cells A2 to A8 for the employee name Castello and sum up the number of files completed by Castello from cells B2 to B8.

Also read: How to change the size of a Google Slide?

SUMIF with Numbers

In this example, we are summing up the rewards for the 205 files completed by different people. Unlike Text criteria, you don’t have to use quotation marks to enclose the numbers as a criterion. You can enter the value with or without the quotation mark, the results will be the same. Refer to the example below.

Step Choose the cell where you want to see the results and type the formula =SUMIF(B2:B8,205,C2:C8) and press Enter.

In this example, it will check from cells B2 to B8 for the 205 files and sum up the rewards of the people who have completed 205 files from cells B2 to B8.

SUMIF with Date

In this example, we are summing up the number of files completed on the date 3 March 2023.

Step Choose the cell where you want to see the results and type the formula =SUMIF(C2:C8,DATE(2023,3,3),C2:C8) and press Enter.

It will check from cells C2 to C8 for the date 3 March 2023 and sum up the number of files completed on 3 March 2023 from cells B2 to B8.

SUMIF with logical operators

You can use the logical operators with the value to compare the values. The logical operators include the following.

= (Equal to)
<> (Not equal to)
> (Greater than)
< (Less than)
<= (Greater than or equal to)
>= (Less than or equal to)

In this example, we have summed up the number of files >220.

Step Choose the cell where you want to see the results and type the formula =SUMIF(B2:B8,”>220″) and press Enter.

It will check from cells B2 to B8 for files less than 220 and sum up the number of files less than 220.

Similarly, you can use other logical operators in the formula as per your own requirement in the spreadsheet.

SUMIF with Wildcard Characters

If you want to sum up the cells for a specific text then you can use wildcard characters; Asterisk (*) or Question mark (?). Add the wildcard character with the text in the criterion to sum up the cells with the related text.

In this example, we have summed up the quantity of all the Moto phones.

Step Choose the cell where you want to see the results and type the formula =SUMIF(A2:A8,”Moto*”,B2:B8) and press Enter.