< Go Back

SUMIF Google Sheets

SUMIF function in Google Sheets is used to sum the numbers in a range based on a condition. SUMIF function is basically designed to sum the numbers based on one condition to meet. It has two parts, SUM and IF. If a range of cells meets the condition, then this function sums the numbers related to that condition. Syntax of SUMIF function in Google Sheets is same as in Excel.Syntax;

=SUMIF(range, criterion, [sum_range])

Where,

range: It is a cells range that is tested against a criterion

criterion: It is a condition to be met. It can contain a value (number, text, date), expression, logical operator, wildcard character, cell reference or another function.

sum_range: It is a cells range to sum the numbers where condition is met.

There are some rules to follow while using SUMIF function correctly;

  • SUMIF function is not case-sensitive.
  • Size of range and sum_range must be equal
  • range and sum_range could be same, if a numeric range is tested against a number as criterion.
  • If criterion contains a text, date, expression, logical operator and wildcard, then it must be enclosed in double quotation marks, such as “t-shirt”“>5”“*”
  • If criterion is a number, cell reference or another function, then it must not be enclosed in double quotation marks , such as B1 or Today()
  • If criterion contains both logical operator and cell reference or another function, then they must be joined together by using ampersand (&), such as “>”&B2, or “>”&Today()

When you enter SUMIF function in Google Sheets an auto-suggest box popups, containing syntax, example, summary related to SUMIF function and explanation of each part of function as shown below.

In this article you will learn how to use SUMIF function in Google Sheets in different criterion cases with examples.

SUMIF function with Text criteria

If you want to sum the numbers in cells range that have a specific text value in a parallel cells range, another column and same row, supplied as criteria.

For example, you have a data set of sales order for various products and you want to sum the amounts of orders for “Apple” product as criterion. So the criterion can be supplied in SUMIF function as text value in double quotation marks or as cell reference without double quotation marks, such as;

=SUMIF(B2:B20,"Apple",D2:D20)

OR

=SUMIF(B2:B20,H2,D2:D20)   

Here,

B2:B20 is range of cells

“Apple” or H2 is criterion as text value or cell reference

D2:D20 is sum_range of cells

SUMIF function with Number criteria

If you want to sum the numbers that meet certain criterion as specified in SUMIF function in Google Sheets, by using comparison operators, like greater than (>), less than (<), greater than equal to (>=), less than  equal to (<=) or Not equal to (<>).

Suppose you want to sum all order amounts that are greater than equal to $5,000. In this case range and sum_range are same, as order amounts are listed in same range and criteria is also applied on same range. In this case SUMIF function can be applied in one of ways;

=SUMIF(D2:D20,">=5000",D2:D20)

OR

=SUMIF(D2:D20,">="&H2,D2:D20)

As you can see criterion expression is supplied in double quotation marks in SUMIF function, such as “>=5000”, but if criterion is supplied as expression and cell reference then they are joined together by ampersand (&), such as “>=”&H2.

SUMIF function with Date criteria

You can sum the numbers based on date criteria by using any of the comparison operators. Date must be supplied in double quotation marks in a format that is understandable by Google Sheets, or date must be supplied as cell reference without double quotation marks, or date must be supplied using a date function like DATE() or TODAY().

Suppose you want to sum the order amounts that are greater than equal to (>=) a criterion date, say 7/1/2018, so you can sum those amounts using SUMIF function in any of the following ways;

=SUMIF(E2:E20,">=7/1/2018",D2:D20)

OR

=SUMIF(E2:E20,">="&H2,D2:D20)

OR

=SUMIF(E2:E20,">="&Date(2018,7,1),D2:D20)

SUMIF function with Blank or Non-Blank cells criteria

You can sum the numbers, based on criteria where cells are blank or non-blank in range of cells using SUMIF function in Google Sheets.

Suppose in your data set some of orders do not have delivery date mentioned, so you want to sum the order amounts based on criteria where delivery date is mentioned (non-blank), and where delivery date is not mentioned (blank).

  • SUMIF function with Blank cells criteriaThis SUMIF formula will sum the order amounts where delivery date is not mentioned in range. Criterion is supplied as double quotation marks without any space in-between, such as “”.

    =SUMIF(E2:E20,"",D2:D20)

  • SUMIF function with Non-Blank cells criteriaThis SUMIF formula will sum the order amounts where delivery date is mentioned in range. Criterion is supplied by using comparison operator Not Equal to (<>) in double quotation marks, such as “<>”, means where cells are not blank. This excludes those amounts where delivery dates are blank.

    =SUMIF(E2:E20,"<>",D2:D20)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar