SUMIFS formula and example in Excel and Google Sheets

Formula, examples and how to use it

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

One of the most popular functions used in Excel and Google Sheets, and returns the sum of a range depending on multiple criteria.

In this exercise, we will analyze the Retail industry revenue in the United States, where retail is criteria_1, and the United States is criteria_2, and revenue will be sum_range. We only need 2 criteria in this example, but SUMIFS takes multiple criteria.

SUMIFS formula and example in Excel and Google Sheets

SUMIFS function in Excel and Google Sheets

SUMIFS FORMULA AND EXAMPLE AND RESULT — EXCEL & GOOGLE SHEETS EXAMPLE

In the above datasheet, let's find out the total revenue for the "Retail" industry in the United States. Since our data set is small we can see total revenue for 2020 is $559,200. If we have 1000s row of the data we can use the SUMIFS function to get the result and the formula will look like this.

=SUMIFS(D3:D7,C3:C7,"Retail",G3:G7,"United States")
- D3:D7 = sum_range
- C3:C7 = criteria_range1
- "Retail" = criteria1
- G3:G7= criteria_range2
- "United States" = criteria2
- // a text criteria within formula must be inside quotation marks " "
=SUMIFS(D3:D7,C3:C7,A11,G3:G7,B11) // criteria as a cell reference
D3:D7 = sum_range
C3:C7 = criteria_range1
- A11 = "Retail" = criteria1
- G3:G7 = criteria_range2
- B11 = "United States" = criteria2
💡 "Retail" = criteria are not case sensitive and "RETAIL" will produce the same result. As long we have the same text within the criteria, we will get the same result
💡 "1 Retail", "1 RETAIL", will NOT produce the same result.
💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel
💡 Below explained, how to sum based on multiple criteria if a criterion contains part of the string

How to SUMIFS cells if contains part of a string

How to sum based on multiple criteria if a criterion contains part of the string?

HOW TO SUMIFS CELLS IF CONTAINS PART OF A TEXT STRING — EXCEL AND GOOGLE SHEETS EXAMPLE

In this example, we sum all cells that include the text "Retail" in the industry in the United States. This search is not case-sensitive, so "Retail" is considered the same as "RETAIL"
=SUMIFS(D3:D7,C3:C7,"*Retail*",G3:G7,"United States")
- D3:D7 = sum_range
- C3:C7 = criteria_range1
- "*Retail*" = criteria1
- G3:G7= criteria_range2
- "United States" = criteria2
- // a text criteria within formula must be inside quotation marks " "
=SUMIFS(D3:D7,C3:C7,"*"&A11&"*",G3:G7,B11) // criteria as a cell reference
- D3:D7 = sum_range
- C3:C7 = criteria_range1
- "*"&A11&"*" = "Retail" = criteria1
- G3:G7 = criteria_range2
- B11 = "United States" = criteria2
💡 Wildcard: The * character allows for any number (including zero) of other characters to take its place.
💡 "Retail" = criteria are not case sensitive and "RETAIL" will produce the same result. As long we have the same text within the criteria, we will get the same result
💡 "1 Retail", "1 RETAIL", will NOT produce the same result.
💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel
💡 Below explained, how to sum based on multiple criteria if a criterion starts with a certain value

How to SUMIFS cells start with a certain value

How to sum based on multiple criteria if a criterion starts with a certain value?

HOW TO SUMIFS CELLS START WITH A CERTAIN VALUE — EXCEL AND GOOGLE SHEETS EXAMPLE

In this example, we sum all cells if the industry starts with "Retail" in the United States. This search is not case-sensitive, so "Retail" is considered the same as "RETAIL"
=SUMIFS(D3:D7,C3:C7,"Retail*",G3:G7,"United States")
- D3:D7 = sum_range
- C3:C7 = criteria_range1
- "Retail*" = criteria1 // criteria must start with "Retail". "1. Retail" will not get the same result
- G3:G7= criteria_range2
- "United States" = criteria2
=SUMIFS(D3:D7,C3:C7,A11&"*",G3:G7,B11) // criteria as a cell reference
- D3:D7 = sum_range
- C3:C7 = criteria_range1
- A11&"*" = criteria1 //criteria must start with "Retail". "1 Retail", "1 RETAIL", will not get the same result
- G3:G7 = criteria_range2
- B11 = "United States" = criteria2
"Retail*": to sum a value start with a certain condition must end with an asterisk (*) like the above example.
Asterisks explanation
- "a" at start = a*
- "b" at end = *b
💡 "Retail" = criteria are not case sensitive and "RETAIL" will produce the same result. As long we have the same text within the criteria, we will get the same result
💡 "1 Retail", "1 RETAIL", will NOT produce the same result.
💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel
💡 Below explained, how to sum based on multiple criteria if a criterion end with a certain value

How to SUMIFS cells end with a certain value

How to sum based on multiple criteria if a criterion end with a certain value?

HOW TO SUMIFS CELLS END WITH A CERTAIN VALUE — EXCEL AND GOOGLE SHEETS EXAMPLE

In this example, we sum all cells if the industry end with "Retail" in the United States. This search is not case-sensitive, so "Retail" is considered the same as "RETAIL"
D3:D7 = sum_range
=SUMIFS(D3:D7,C3:C7,"*Retail",G3:G7,"United States")
- C3:C7 = criteria_range1
- "*Retail" = criteria1
- G3:G7= criteria_range2
- "United States" = criteria2
=SUMIFS(D3:D7,C3:C7,"*"&A11,G3:G7,B11) // criteria as a cell reference
- D3:D7 = sum_range
- C3:C7 = criteria_range1
- "*"&A11 = criteria1 //criteria must end with "Retail". "Retail 1", "RETAIL 1" will not get the same result
- G3:G7 = criteria_range2
- B11 = "United States" = criteria2
"*Retail": to sum a value end with a certain condition must start with an asterisk (*) like the above example.
Asterisks explanation
- "a" at start = a*
- "b" at end = *b
💡 "Retail" = criteria are not case sensitive and "RETAIL" will produce the same result. As long we have the same text within the criteria, we will get the same result
💡 "Retail 1", "RETAIL 1", will NOT produce the same result.
💡 Access the Google Sheets here used in this exercise. All functions on this page are compatible with Microsoft Excel and the same analysis can be made in Excel

Other widely used Excel and Google Sheets formulas explained

Others Formulas

Date and time functions

Number based criteria

Text-based criteria

Extract functions

Coming | Subscribe here for the custom Excel/Sheets formulas E-book (PDF) >

Google Sites

Report abuse