One of the most popular functions used in Excel and Google Sheets, and returns a conditional sum across a range. In this exercise, we will analyze the total revenue of the top 5 company 2020 and the same method can be used for profits and employees.
In the above datasheet, let's find out the total revenue for the "Oil and gas" industry. Since our data set is small we can simply sum the individual cell D4+D7 and we will get the result. If we have 1000s row of the data we can use the SUMIF function to get the result and the formula will look like this.
=SUMIF(C3:C7,"Oil and gas",D3:D7) // a text criteria within formula must be inside quotation marks " "
=SUMIF(C3:C7,B12,D3:D7) // criteria as a cell reference
C3:C7 = criteria_range
"Oil and gas" = criteria
D3:D7 = sum_range
💡 "Oil and gas" = criteria are not case sensitive "Oil and Gas", "OIL AND GAS", "Oil And Gas" will produce the same result. As long we have the same text within the criteria, we will get the same result
💡 "1 Oil and Gas", "1 OIL AND GAS", "1 Oil And Gas" 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 if a text contains part of the string
=SUMIF(C3:C7,"*Oil and gas*",D3:D7) // a text criteria within formula must be inside quotation marks " "
=SUMIF(C3:C7,"*"&B11&"*",D3:D7) // criteria as a cell reference
C3:C7 = criteria_range
"*Oil and gas*" = criteria
D3:D7 = sum_range
=SUMIF(C3:C7, "Oil and gas", D3:D7) this will return 0 value in the above data set
💡 Wildcard: The * character allows for any number (including zero) of other characters to take its place.
💡 In this example, it’s used to find all cells that include the text "Oil and gas". This search is not case-sensitive, so "Oil and gas" are considered the same as "OIL AND GAS" or "Oil And Gas".
💡 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 if a text contains part of the string
=SUMIF(C3:C7,"1*",D3:D7) // a text criteria within formula must be inside quotation marks " "
=SUMIF(C3:C7,B11&"*",D3:D7) // criteria as a cell reference
C3:C7 = criteria_range
"1*" = criteria // must start with 1
D3:D7 = sum_range
=SUMIF(C3:C7, "1", D3:D7) this will return 0 value in the above data set
"1*": to sum a value start with a certain condition must be followed by an asterisk (*) like the above example.
Asterisks explanation
"a" at start = a*
"b" at end = *b
💡 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 if a text contains part of the string
=SUMIF(C3:C7,"*gas",D3:D7) // a text criteria within formula must be inside quotation marks " "
=SUMIF(C3:C7,"*"&B11,D3:D7) // criteria as a cell reference
C3:C7 = criteria_range
"*gas" = criteria // must end with gas
D3:D7 = sum_range
=SUMIF(C3:C7, "gas", D3:D7) this will return 0 value in the above data set
"*gas": 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
💡 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
