Excel Formulas
In this article, you will learn how to use the SUMIF formula in Excel.
The SUMIF formula in Excel allows you to sum up a range of cells based on a specific condition.
The SUMIF function in Excel is helpful when you need to add up a range of cells based on a specific condition or criteria. This function can be beneficial in a variety of situations, such as
The syntax for the formula is as follows:
"range" is the range of cells to which you want to apply the criteria to
"criteria" is the condition that must be met for the cells to be included in the sum.
"sum_range" is the range of cells that you want to sum. This argument is optional, and if this is not specified, the "range" argument is used as “sum_range”.
Note 1: When you input the “criteria” argument directly in the formula by typing it, note that any text criteria or any condition containing logical or mathematical signs need to be enclosed by quotation marks. If you enter a numeric standard, double quotation marks are not necessary.
Note 2: The ranges sizes for the “range” and “sum_range” arguments should be the same. Otherwise, the result provided by the formula may be incorrect, though it may still run the calculation. (The formula considers the “sum_range” as a range starting from the first cell in the “sum_range” but having the same dimension as the range specified in the “range” argument.
Before looking at sample SUMIF formulas containing numeric criteria, get yourself familiarized with logic operators, such as “>” (greater than) and “<” (less than).
Assume you have the dataset in the picture below and want to compute the Total Sales amount that meets a specific criterion, such as Total Sales from clients with Sales over 5,000. The formula and its arguments are as follows:
"range": $E$3:$E$12
"criteria": ”>5000”
"sum_range": $E$3:$E$12 (This can be omitted in this case)
You can see other examples in the screenshot below as well. Note that when you use an equal operator “=” with a specific number, you can directly input the particular number without the operator, as shown in the fifth example formula below.
You can use a date as the “criteria” argument in the SUMIF function, almost similar to a number criterion. So, you can combine logic operator(s) with a date to create a condition. However, note that you need to enclose a date with quotation marks when you directly enter it in the formula, such as the following formula:
"range": $D$34:$D$43
"criteria": ”>=4/30/2022”
"sum_range": $E$34:$E$43 (This can’t be omitted in this case)
You can see two more sample formulas with date conditions in the following picture.
You must remember that when you manually insert a text condition as the “criteria” argument, you always need to enclose them with double quotes. Also, you can use the operators, such as “<>” or the wildcards, such as “*” and “?” together with a text string to form a condition. For example, if you want to calculate the total sales amount from the product “Apple”, you can apply the following formula to the dataset.
"range": $C$56:$C$65
"criteria": ”Apple”
"sum_range": $E$56:$E$65 (This can’t be omitted in this case)
You should bear in mind that this formula is not case-sensitive. So, for example, you can get the same result when you use “apple” instead of “Apple” as “criteria” in the formula. The picture contains four more sample SUMIF formulas with text conditions if you are interested.
Although you can manually enter “criteria”, as shown in the examples above, instead, you can use the cell reference to input the “criteria” argument. We recommend the cell reference approach because it gives you more visibility and clarity about the condition as the requirement can be seen in a cell and allows you to change the condition more quickly. You don’t need to put your cursor on the cell containing the formula or open the cell by hitting the F2 button to see in detail or revise the function. Note that you don’t need to enclose it with quotation marks when you put a criterion in a cell, as shown in the picture below.
Both the SUMIF and SUMIFS functions in Excel are used to sum a range of cells based on one or more conditions, but the main difference between the two functions is that the SUMIFS formula allows you to use multiple criteria to sum a range of cells, while the SUMIF function enables you to use only one criterion. In this regard, we could say the SUMIFS function is more versatile and flexible, allowing you to use multiple conditions to aggregate a range of cells, while the SUMIF formula is more straightforward and explicit but less flexible, only allowing you to use one requirement.
Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows, update numbers in real-time, and save time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. Are you interested in this product but are an Excel user? That’s not a problem at all. You can connect Google Sheets to Excel quickly.
To learn more about LiveFlow, book a demo.
If you don’t get the solution you are looking for in this article, or you have further questions related to mathematics or statistics, you may find the answers in the following articles.
Go to the following articles to learn basic formulas in Google Sheets.
How to Do Math in Google Sheets for Beginners
How to Use SUM Function in Google Sheets
ADD Function in Google Sheets: Explained
MINUS Function in Google Sheets: Explained
DIVIDE Function in Google Sheets: Explained
MULTIPLY Function in Google Sheets: Explained
PRODUCT Function in Google Sheets: Explained
How to Use SUM Function in Google Sheets
How to Use SUMPRODUCT Formula in Google Sheets
How to Use MAX Function in Google Sheets
How to Use MIN Function in Google Sheets
How to Use MEDIAN Function in Google Sheets
How to Use AVERAGE Formula in Google Sheets
How to Use MODE Function in Google Sheets
MOD Function in Google Sheets: Explained
Navigate to the pages below to learn how to sum, count, or average numbers with a condition or multiple conditions.
How to Use COUNTIF Function in Google Sheets - count the number of cells that meet a requirement
How to Use COUNTIFS function in Google Sheets - count the number of cells that meet multiple requirements
AVERAGEIF Function in Google Sheets: Explained - average the figures that meet a standard
AVERAGEIFS Function in Google Sheets: Explained - average the figures that meet multiple standards
AVERAGE.WEIGHTED Function in Google Sheets: Explained - use this formula to calculate the weighted average
MAXIFS Function in Google Sheets: Explained - seek for the maximum value that meets specific criteria
MINIFS Function in Google Sheets: Explained - seek for the minimum value that meets specific criteria
Visit the following post if you are interested in learning how to count the number of specific cells.
COUNT Function in Google Sheets: Explained - count the number of cells containing numeric values (except for blank ones)
COUNTA Function in Google Sheets: Explained - count the number of cells incorporating all types of values containing text and date, except for blank ones
COUNTBLANK Function in Google Sheets: Explained - count the number of blank cells
COUNTUNIQUE Function in Google Sheets: Explained - count the number of unique cells
You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides
Learn how to do this step-by-step in the video below 👇
