Open In App

Excel SUMIF Function: Formula, How to Use and Examples

Last Updated: 21 Nov, 2024

K

Kritidipta Ghosh
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

The SUMIF function in Excel is a versatile tool that enables you to sum values based on specific criteria, such as a true or false condition. This feature makes it perfect for conditional summation tasks like totaling sales over a particular amount, adding up expenses in specific categories, or summing scores above a threshold. Whether you’re working with Excel formula SUMIF for single criteria or expanding to SUMIFS with multiple criteria, this Excel tutorial will show you how to efficiently filter and sum data.

In this article, we’ll explain SUMIF function in Excel and demonstrate how to use SUMIF with multiple criteria, along with Excel SUMIF examples that showcase its flexibility.

SUMIF Function in Excel with Examples

SUMIF Function in Excel with Examples

Table of Content

What is the SUMIF Function in Excel

The SUMIF function in Excel allows you to sum data within a specified range based on a single condition, making it an essential part of Excel formulas for tasks like filtering totals by category, date, or other attributes. Whether you need to sum if a certain text or sum if dates fall within a particular range, this function proves to be a handy tool. Advanced scenarios may require combining SUMIF with other functions like MATCH function in Excel or FILTER Excel function for refined data analysis.

Excel Formula SUMIF: Syntax

SUMIF(range, criteria, [sum_range])

This function accepts range, criteria(condition) which checks things like >, <,=, and sum_range(optional argument) as its argument and returns the sum of the sum_range or the range according to the given arguments. The arguments are discussed below.

SUMIF Function Parameters

Arguments of the SUMIF function are the following:

  • range: The range of cells you want to evaluate based on the criteria.
  • criteria: The condition that determines which cells to sum.

Note: sum_range (optional): The range of cells to sum if different from the range to evaluate. If sum range not mentioned, it calculates the sum of same range as criteria condition.

Return Value:

This function sums the numbers in the given range and returns the numerical value of the sum. 

How to Use SUMIF Function in Excel

The SUMIF function is a powerful tool, but it only takes a few simple steps to use it effectively. Here are the following steps to effectively use SUMIF in Excel formula.

Step 1: Open MS Excel

Step 2: Select the Cell for the Result

Select the cell in which you want to display the result of the SUMIF Function.

SUMIF Function in Excel

Select the cell to enter result

Step 3: Enter the SUMIF Function and specify the range

Start typing the Formula starting with the ‘=SUMIF(‘ in the selected cell and also specify the range of the cells you want to evaluate. For example, if you want to evaluate cells ‘B2‘ to ‘B11‘, type ‘B2:B11‘,.

SUMIF Function in Excel

Type the Formula

Step 4: Define the Criteria

Enter the criteria for summing the values. For example, if you want to sum cells where the value is “Apple”, Select cell E4(Apple).

Step 5: Drag the Formula

Step 6: Preview the Result

SUMIF Function in Excel

Preview the Result

How to Use Excel SUMIF with Dates

The SUMIF function with dates allows you to add values based on specific date criteria, ideal for tracking sales or events within a certain timeframe. For example, to sum all values after January 1, 2023, enter =SUMIF(B2:B11, “>01/01/2023”, C2:C11).

Step 1: Open MS Excel

Step 2: Select the Cell

Select the cell in which you want to display the output.

SUMIF Function in Excel

Select a Cell

Step 3: Enter the SUMIF Function and specify the range

Start by typing =SUMIF( in the selected cell and enter the range of cells the dates you want to evaluate. For example, if your dates are in column B from B2to B10, type B2:B10,.

SUMIF Function in Excel

Enter SUMIF function and specify range

Step 4: Define the Criteria

Enter the date criteria. For instance, if you want to sum values for dates after January 1, 2023, type ” >01/01/2023“,.

SUMIF Function in Excel

Define the Criteria

Step 5: Enter the Sum Range

Enter the range of cells that contain the values you want to sum. For example, if these values are in column B from B2 to B10, type B2:B10).

SUMIF Function in Excel

Enter the SUM Range

Example SUMIF Function

An Excel sheet has been taken as an example and the SUMIF function has been used in several formats.

Input:

Coding Team names(Column A) No. of members(Column B) points(Column C)
GFG_CODERS 4 200
Acex_coders 5 197
Poisionous_python 3 150
Megatron 4 130
Bro_coders 6 110
Kotlin_coders 2 100
Gaming_coders 3 50

Then we will apply the SUMIF() function to the above table:

SUMIF() function What the function does Output result
=SUMIF(B2:B8, “>4”, C2:C8) If the no. of members in column B is greater than 4 then add the corresponding points of column C. 307
=SUMIF(B2:B8, 4, C2:C8) If the no. of members in column B is equal to 4 then add the corresponding points of column C. 330
=SUMIF(A2:A8, “GFG_CODERS”, C2:C8) Search for “GFG_CODERS” in column A and add the corresponding points in column C. 200
=SUMIF(C2:C8, “>110”) Here the sum_range argument is not provided. So it will check the cells of C column and if the points are greater than 110 add it to the result. 677
=SUMIF(A2:A8, “*rs”, C2:C8) Here it will find the names of the teams ending with “rs” / “RS” in column A and add the corresponding points to the sum. 657

Output:

SUMIF Function in Excel

Output of Excel Sheet

  • The Output after using the SUMIF Formula
SUMIF Function in Excel

After Using the SUMIF Formula

How to Use SUMIFS with Multiple Criteria in Spreadsheet

Below are some examples of SUMIF Function in Excel:

Step 1: Enter the data Set

image-

Enter the Data Set

Step 2: Enter the Formula for multiple criteria

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

  • sum_range: The range to add.
  • criteria_range1, criteria1: The first range and condition pair.
  • [criteria_range2, criteria2] (optional): Additional ranges and conditions.

Example : To sum the Sales where Product is “Product A” and Category is “Electronics”:

=SUMIFS(C2:C10, A2:A10, “Product A”, B2:B10, “Electronics”)

image

Enter the Formula

Conclusion

The SUMIF function is an essential tool for anyone working with data in Excel, allowing you to add up values based on specific criteria quickly. With a solid understanding of its syntax and practical applications, you’re now equipped to use SUMIF in various scenarios, from tracking budgets to analyzing sales. Start using SUMIF in your projects to save time and improve the accuracy of your data analysis!

SUMIF Function in Excel – FAQs

How do you use SUMIF in Excel?

The SUMIF function in Excel adds all numbers in a range that meet a specific criterion. We can use it by using the following syntax: SUMIF(range, criteria, [sum_range]).

  • range: the cells you evaluate with the criteria.
  • criteria: the condition that determines which cells to add.
  • sum_range (optional): the cells to sum. If omitted, Excel sums the cells in range.

What is the correct SUMIF function?

The correct SUMIF function in Excel allows you to sum the values in a range that meet a specific criterion which is as follows:

“SUMIF(range, criteria, [sum_range])”

What are sum SUMIF and SUMIFS functions?

Excel offers several powerful functions to perform summing operations, each tailored for different needs: SUM, SUMIF, and SUMIFS.

The SUM function adds all the numbers in a range of cells.

The SUMIF function adds the values in a range that meet a single specified criterion.                         

What is SUMIFS function in excel with multiple criteria?

The SUMIF Function in Excel in Excel with multiple criteria is:

“SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)”

What is Advanced SUMIFS function in excel?

The SUMIFS function in Excel can be used in more advanced ways to handle complex scenarios, involving multiple criteria across different ranges. Here are some advanced techniques to get the most out of the SUMIFS function.

For example: “=SUMIFS(D2:D9, B2:B9, “Apple”, C2:C9, “North”, D2:D9, “>100″)”



Article Tags :

K

Kritidipta Ghosh
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

S

Sukriti Chawla

SUMIF in Google Sheets with formula examples

article_img
Mastering data analysis with the SUMIF function in Google Sheets! This essential tool allows you to add up values that meet specific criteria, making it perfect for tracking expenses, analyzing sales data, and more. With SUMIF in Google Sheets, you can effortlessly sum values based on conditions you set, streamlining your data management tasks.SUMIF in Google SheetsTable of ContentSyntax and Basic Uses of SUMIF in Google SheetsSUMIF Formula Examples in Google Sheets1. SUMIF Formulas with Text Criteria3. How SUMIF-EXACT/FIND formula works4. SUMIF formulas for numbers5. Google Sheets SUMIF formulas for dates6. Sum based on blank or non-blank cells7. Google Sheets SUMIF with multiple criteria (OR logic)Syntax and Basic Uses of SUMIF in Google SheetsUnderstand the basic syntax and uses of the SUMIF function in Google Sheets to get started with this powerful tool.=SUMIF(range, criterion, sum_range)range: Range of cells where the condition has to be checked.criterion: The condition that has to be matched with the cells specified in the range above.sum_range(optional): The range of cells with numerical data that has to be summed up. If this is not specified, then the first range is checke
Read More

G

Gaurav Sharma

Using the SUMIF Function to Add Cells if They Contain Certain Text

article_img
In Excel, sometimes we want to sum up the values based on some specific text values. Such as the cells which have text written as "ME" will be added, so we will see how to achieve this requirement. We can also use VLOOKUP with case sensitivity to achieve the above requirement, but EXCEL provides us a function dedicated to this. So we will use a function called "SUMIF" to implement the requirement. First, let's look into the SUMIF function. The SUMIF Function: SUMIF function can add specific cell(s) values, based on the criteria, or the keyword it will use to filter out the values and add only those cells values, which are having this keyword associated with them. Syntax: SUMIF(range,"criteria or keyword",cell values to be added) Here, The range specifies the list of cells on which the criteria will be matched,i.e the cells matching the above keyword, only their value will be considered for the sum.The criteria or keyword highlights the matching keyword, that will be used by the SUMIF function.The cell values will be that column in which the values will be picked up for sum. Here the cells which were selected in the range column, only their value will be considered for summing in th
Read More

M

Mansi Sharma

How to use IRR Function in Excel: Formula + Examples

article_img
How to Calculate Internal Rate of Return in Excel: Quick StepsEnter Your Cash FlowsEnter IRR Formula >> Press Enter Preview ResultsEver wondered how businesses decide which projects are worth investing in? The Internal Rate of Return (IRR) is a key financial metric that helps evaluate the profitability of investments. But calculating it manually can be tricky—thankfully, Excel’s IRR function makes it easy. Whether you’re analyzing uneven cash flows, comparing multiple projects, or just learning about financial metrics, this guide will show you how to calculate IRR in Excel step by step.In this article, we’ll break down the Excel IRR formula with examples, explain the difference between IRR vs. NPV, and even cover advanced calculations like XIRR and MIRR. By the end, you’ll know how to use the IRR function like a pro and make smarter investment decisions.How to Calculate IRR FunctionTable of ContentWhat Is Internal Rate of Return (IRR)Formula for Calculating Internal Rate of Return (IRR) in ExcelHow to Calculate IRR in Microsoft Excel: Step by Step GuideHow to use XIRR Function in Excel (IRR for Irregular Cash Flows)How to use MIRR Function in Excel (Modified Internal Rate of
Read More

K

Kritidipta Ghosh

How to Use HLOOKUP in Excel (Formula & Examples)

article_img
Excel HLOOKUP Function - Quick StepsSelect the cell Enter the HLOOKUP formulaInput arguments>>Press Enter Excel is packed with features that make handling and analyzing data easier. One of these handy tools is the HLOOKUP function, which is great for finding information in tables that are organized horizontally. The "H" in HLOOKUP stands for "horizontal," meaning it looks across the top row of a table to find a value, and then retrieves data from a specific row underneath. Whether you're working with a wide table or need to quickly find data in specific rows, HLOOKUP can help make your tasks faster and more efficient.In this article, you will learn how to use the HLOOKUP Function in Excel, understand its HLOOKUP syntax, and explore practical HLOOKUP examples in Excel like working with multiple worksheets or even different workbooks.Try using the new XLOOKUP function, An improved version of HLOOKUP that works in any direction and gives the exact match by default. Excel HLOOKUP Function with Formula and ExamplesTable of ContentWhat is the HLOOKUP Function in ExcelHow to Use HLOOKUP Formula in ExcelExcel HLOOKUP Function with Formula ExamplesHow to Use HLOOKUP Across Two Workshe
Read More

K

Kritidipta Ghosh

How To Use MATCH Function in Excel (With Examples)

article_img
Finding the right data in large spreadsheets can often feel like searching for a needle in a haystack. This is where the MATCH function in Excel proves invaluable. The MATCH function helps you locate the position of a specific value within a row or column, making it a cornerstone of efficient data management and analysis.In this guide, we’ll explore how to use MATCH in Excel, discuss its syntax, and walk through MATCH Excel examples that showcase its practical applications. Whether you're working on a project that involves dynamic lookups or need to pair it with other Excel lookup functions, the MATCH function simplifies complex tasks and empowers you to handle data more effectively.MATCH Function in ExcelWhat is the MATCH Function in ExcelThe MATCH function returns the position of a value in a row, column, or table, rather than the actual value itself. For Example, if you’re looking for the number "50" in a list of numbers and it’s the 5th item, the function will return the number 5.Syntax of the MATCH Function=MATCH(lookup_value, lookup_array, [match_type]) Here, the [match_type] value denotes whether the user wants an exact match or an approximate match. Arguments:lookup_value:
Read More

A

Ankur Pal

Difference Between Array Formula and Regular Excel Formula

article_img
An array formula performs multiple calculations and processes multiple values at once, the array formula performs operations depending on the condition we have used with the array formula. Whereas the Regular excel formula performs operations on an individual value. Before understanding the difference between the two, let's first understand the definitions of each formula in detail. Regular Excel Formula In order to apply the regular excel formula, we require a dataset. For example, if we have 10 different numbers and we are asked to find out the top 3 smallest numbers. If we will use the regular excel formula, =SMALL(A2:A11, 3) we will get only the 3rd smallest value. Note that, we will not get all 3 smallest values instead we will get only the 3rd smallest value from our data source. Array Formula Using the array formula, we can easily find the top 3 smallest values from our data source in a single step. For this Select 3 Cells > Add Following Array Formula =SMALL(A2:A11,{1;2;3}) > Ctrl + Shift + Enter. As we click Ctrl + Shift + Enter, Excel will convert our formula into an array formula. And, find the top 3 smallest values from our data source. What is Array Formula?
Read More

G

Gaurav Sharma

How to Decide Which Excel Lookup Formula to Use?

article_img
In Excel, you have a ton of methods to look upon the specific types of values, but the optimal method chosen is what creates a difference. So, what mindset we need to use specific lookup methods in a situation? That's what we will look at here. But, first, let's take a look at an example from real life why is it necessary to choose the most optimal approach for lookups. Example: Suppose you work at a firm, where lots of revenue sheets(or receipts), and regular employee update sheets come in regularly, but the availability of computers in that region is almost NULL.So, you will use your senses to compute these sheets. Now, you have many approaches, either to read the total of revenues in every sheet column by column, or row by row, or computing small numbers first, and then add or subtract(according to a sheet) big numbers from them, use calculators to compute the sheets, and the ways will be getting more broad, and more in numbers. Now, if you try to compute regular employee sheets, which contain the regular performance of employees along with the daily wage they got, and whether someone left the firm or not. Here also many approaches are possible, as they contain names, numbers, p
Read More

K

Kritidipta Ghosh

Excel VLOOKUP Function - Excel Guide for Beginners

article_img
How to do Vlookup in Excel - Quick StepsPrepare Your DataEnter the VLOOKUP Formula >>Press EnterUse a Cell Reference for Flexibility Copy the Formula for Multiple RowsThe VLOOKUP function is one of the most widely used tools in Excel for looking up and retrieving data from a table. Whether you’re working with a single sheet or multiple workbooks, VLOOKUP allows you to search for a value in one column and return a related value from another. If you're new to Excel or still getting the hang of formulas, this guide is for you. We will cover everything from the basics of how VLOOKUP works to practical examples that show how to search data across sheets, troubleshoot common issues, and master the VLOOKUP formula for any task.Excel VLOOKUP FunctionWhat is the VLOOKUP Function in ExcelThe term VLOOKUP stands for Vertical Lookup. It is designed to search for a specific value in the first column of a table (lookup column) and retrieve corresponding data from a different column in the same row.Syntax of VLOOKUP FormulaVLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Arguments:1. lookup_value: The value you want to search for (e.g., a specific ID, name, or product code).
Read More

P

Prachi Sharma

Excel Date Functions with Formula Examples

article_img
There are many functions in Microsoft Excel that may be used to work with dates and timings in Excel. Each function completes a straightforward task, but by combining numerous functions into a single formula, you may handle trickier and more complicated problems. The purpose of discussing DATE functions in Excel is to help different people perform more complex and challenging tasks by combining several functions within one formula. The DATE function is used to calculate dates in Excel. To work with these formulas you first need to insert the dates in Excel. Excel provides different functions to work with dates & times such as TODAY, NOW, WEEKDAY, EOMONTH, etc. which we will discuss here with examples. Excel DATE function ListDATE Function Get the Current date and TimeTODAY- It returns today's date.NOW- It returns the current date and time.Converts Dates to / From textDATEVALUE- Helps to Convert date in the text format to date format TEXT- It helps to convert a date to a text value.Retrieve dates in ExcelDAY- It returns the day of the monthMONTH- It is used to return the month of a specified dateYEAR- It is used to return the year of a specified dateEOMONTH- It returns the last
Read More

R

Rahul kushwaha

How to use Copilot in Excel with Examples (Step-by-Step Guide)

article_img
As of 2024, Microsoft introduced a new feature called Copilot for Excel, part of the broader Microsoft 365 suite, designed to enhance productivity and streamline data manipulation using AI-powered tools. The introduction of Excel Copilot is transforming how professionals across industries manage and interpret their data. This AI-driven tool enhances productivity, automates repetitive tasks, and simplifies complex data analysis. In this detailed guide, we'll explore how to effectively use Copilot in Excel, complete with practical examples to illustrate its capabilities.How to use Copilot in Excel with ExamplesTable of ContentWhat is Copilot in Excel?How to Access Copilot in ExcelExcel Copilot Advantages in ExcelHow to Use Copilot in Excel (November 2024)Different Ways to use Copilot in Excel (Including Copilot Prompt)What is Copilot in Excel?Microsoft Excel Copilot is an innovative, AI-powered feature designed to enhance productivity and streamline data processing tasks within Excel. As a part of the Microsoft 365 suite, Excel Copilot transforms how users interact with data by offering advanced artificial intelligence capabilities that simplify complex operations, automate repetitiv
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox