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 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 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 SUMIFS CELLS END WITH A CERTAIN VALUE — EXCEL AND GOOGLE SHEETS EXAMPLE