How to use SUMIF in Excel

Q: What is the syntax of the SUMIF function in Excel?

The SUMIF function syntax is =SUMIF(range, criteria, [sum_range]) , where range is the cells evaluated by the criterion, criteria defines the condition to meet, and [sum_range] is the optional range of cells to sum. If omitted, Excel uses the range itself.

Q: What comparison operators can I use with SUMIF?

You can use > (greater than), < (less than), >= (greater than or equal), <= (less than or equal), <> (not equal), = (equal), * (asterisk wildcard), and ? (question mark wildcard) as criteria in the SUMIF function.

Q: How do I sum values for a specific item using SUMIF?

To sum values for a specific item, set your range as the column containing the item names, your criteria as the specific item (e.g., "strawberry"), and your [sum_range] as the column with the values to sum. For example, summing strawberry expenses gave $53.00 in the article's example.

Q: How can I use SUMIF with date criteria to sum values before a specific date?

Use the < operator combined with the date as your criteria. For example, to sum revenue before 12/01/24, you would write "<"&DATE(2024,1,12) or "<12/01/24" as the criteria, with the Dates column as range and Revenue column as sum_range, yielding $2,050.00 in the example.

Q: What common error should I avoid when using SUMIF?

Ensure that the range and the sum_range have the same number of rows and columns to avoid errors. Mismatched ranges are a frequent cause of incorrect results or formula errors in SUMIF calculations.

Q: How can I centralize data from multiple sources for analysis in Excel?

You can use data integration platforms like Kondado to connect over 80 different sources directly to your spreadsheets. For importing data specifically, explore importing to spreadsheets solutions that automate data collection and keep your Excel analyses up-to-date without manual copying.

Start replicating your data now

Before we start exploring the SUMIF function, let's first understand what Excel is. Microsoft Excel is a powerful spreadsheet widely used across various fields, from accounting to data analysis. With its ability to process and organize large volumes of information, Excel stands out as an essential tool for professionals and students.

What is SUMIF?

The SUMIF function in Excel allows for the summation of values based on specific criteria. Ideal for accounting, data analysis, and other situations where conditional value summation and spreadsheet control are necessary, it offers flexibility and precision when working with large volumes of information. In this article, we will explore with examples how to apply the SUMIF function in Excel, highlighting its importance and versatility.

Understanding the SUMIF Function:

The SUMIF function allows summing values in a range that meet a defined criterion. Its syntax is =SUMIF(range, criteria, [sum_range]), where:

range: Is the set of cells that will be evaluated by the criterion.
criteria: Defines the condition that cells must meet to be included in the sum.
[sum_range]: (Optional) Is the range of cells you want to sum. When not specified, Excel uses the "range."

The criteria accepted by the function are:

(greater than)
< (less than)
= (greater than or equal)
<= (less than or equal)
<> (not equal)
= (equal)
* (asterisk)
? (question mark)

Tip: Ensure that the "range" and the "sum_range" have the same number of rows and columns to avoid errors.

Understanding in Practice

Example 1

In this example, we have a list of expenses at the market during the month, as shown in the image below, and want to know how much was spent on strawberries only.

For this, we will use the SUMIF function, so that the "range" is the "Fruits" column, the "criteria" will be the fruit "strawberry," and the "[sum_range]" will be the "Value" column.

Thus, we arrive at a total of $53.00 spent on strawberries in the month.

Example 2

In this example, we have the following scenario: a store that sells car parts wants to know the store's revenue up to 12/01/24. For this situation, we will use the table below.

The "range" will be the "Dates" column, and before we add the "criteria," we will write "<&" in front to indicate that the selected dates must be earlier than the "criteria," which is 12/01/24. Finally, the "[sum_range]" will be the "Revenue" column.

The result obtained was $2,050.00.

Centralizing data in Excel

Enhance your skills in Excel and make data analysis more efficient with the SUMIF function and transform your data management with Kondado Our platform enables the integration of data from over 80 different sources, paving the way for various in-depth and comprehensive analysis. This is your chance to improve your Excel skills with the SUMIF function and discover the possibilities that Kondado offers for your data work. Try our platform now for free and without the need for a credit card by clicking here. Transform your data analysis approach today!

Conclusion

Mastering the SUMIF function in Excel is fundamental for those working with large data sets, offering an efficient and quick way to perform conditional calculations. This feature significantly expands the analytical and data management capabilities in Excel.

Now you have a practical guide on how to do SUMIF in Excel, a valuable addition to any data analysis. Continue exploring and improving your Excel skills to get the most out of your spreadsheets.

Frequently asked questions

What is the syntax of the SUMIF function in Excel?▼

The SUMIF function syntax is =SUMIF(range, criteria, [sum_range]), where range is the cells evaluated by the criterion, criteria defines the condition to meet, and [sum_range] is the optional range of cells to sum. If omitted, Excel uses the range itself.

What comparison operators can I use with SUMIF?▼

You can use > (greater than), < (less than), >= (greater than or equal), <= (less than or equal), <> (not equal), = (equal), * (asterisk wildcard), and ? (question mark wildcard) as criteria in the SUMIF function.

How do I sum values for a specific item using SUMIF?▼

To sum values for a specific item, set your range as the column containing the item names, your criteria as the specific item (e.g., "strawberry"), and your [sum_range] as the column with the values to sum. For example, summing strawberry expenses gave $53.00 in the article's example.

How can I use SUMIF with date criteria to sum values before a specific date?▼

Use the < operator combined with the date as your criteria. For example, to sum revenue before 12/01/24, you would write "<"&DATE(2024,1,12) or "<12/01/24" as the criteria, with the Dates column as range and Revenue column as sum_range, yielding $2,050.00 in the example.

What common error should I avoid when using SUMIF?▼

Ensure that the range and the sum_range have the same number of rows and columns to avoid errors. Mismatched ranges are a frequent cause of incorrect results or formula errors in SUMIF calculations.

How can I centralize data from multiple sources for analysis in Excel?▼

You can use data integration platforms like Kondado to connect over 80 different sources directly to your spreadsheets. For importing data specifically, explore importing to spreadsheets solutions that automate data collection and keep your Excel analyses up-to-date without manual copying.

Written by

Thassyo Pereira·Published 2024-02-07·Updated 2026-04-25