Learn how to use SUMIF in Excel with one condition to calculate specific totals for sales, expenses, products, and payments. Includes easy formulas, practical examples, and step-by-step instructions for beginners.
How to Use SUMIF with One Condition in Excel (Formula & Examples)
Microsoft Excel makes it easy to calculate totals from large datasets, but sometimes adding every number in a column is not what you need. You may want to calculate total sales for one particular product, find the total expenses for a specific category, or add payments received from a particular customer.
This is where the SUMIF function in Excel becomes useful.
SUMIF allows you to add numbers that meet a specific condition. Instead of manually filtering your data and selecting individual cells, you can write one formula and let Excel calculate the result automatically.
In this guide, you'll learn how to use SUMIF with one condition, understand its syntax, and explore practical examples that you can apply to everyday Excel tasks.
What Is the SUMIF Function in Excel?
The SUMIF function is a built-in Excel formula that adds values from a range of cells when they meet a condition you specify.
For example, imagine you have a sales report containing different products and their sales amounts. If you want to calculate the total sales of laptops only, SUMIF can find all rows containing "Laptop" and add their corresponding sales amounts.
This function is particularly helpful when working with:
Monthly sales reports
Employee salary records
Business expenses
Customer payment records
Product-wise sales summaries
Inventory and stock reports
The biggest advantage is that you don't need to rearrange or manually filter your data every time you want a specific total.
SUMIF Formula Syntax in Excel
Before using the function, it's important to understand its basic structure.
Formula:
=SUMIF(range, criteria, [sum_range])
The formula contains three arguments:
| Argument | Description |
|---|---|
| range | The cells Excel checks to find the condition. |
| criteria | The condition that determines which cells should be included. |
| sum_range | The actual cells containing numbers to add. |
The first two arguments are required, while the third is optional.
If you leave out sum_range, Excel adds the cells in the original range that meet the condition.
How to Use SUMIF with One Condition
Let's understand the formula with a practical example.
Suppose you manage a small electronics shop and maintain a sales record like this:
| Product | Sales Amount |
|---|---|
| Laptop | 45,000 |
| Mobile | 20,000 |
| Laptop | 55,000 |
| Keyboard | 2,000 |
| Mobile | 25,000 |
| Laptop | 35,000 |
You want to calculate the total sales generated by laptops.
Follow these steps:
Step 1: Organise your data
Enter product names in Column A and their corresponding sales amounts in Column B.
Step 2: Select an empty cell
Click the cell where you want the total laptop sales to appear, such as D2.
Step 3: Enter the SUMIF formula
Type the following formula:
=SUMIF(A2:A7,"Laptop",B2:B7)
Step 4: Press Enter
Excel checks the product names in A2, identifies the rows containing "Laptop", and adds the corresponding amounts from B2.
<result> </result>
Final Result:
45,000 + 55,000 + 35,000 = 135,000
Your total laptop sales are 135,000.
This is much faster than manually finding and adding individual sales figures.
Practical SUMIF Formula Examples
The SUMIF function works with different types of conditions. Here are some useful examples.
Calculate Total Sales for a Specific Product
Suppose your product names are in Column A and sales amounts are in Column B.
To calculate the total sales of mobile phones, use:
=SUMIF(A2:A100,"Mobile",B2:B100)
Excel adds all sales amounts associated with the product name "Mobile".
Calculate Total Expenses for One Category
Imagine you maintain a monthly expense sheet with categories in Column A and expenses in Column B.
| Expense Category | Amount |
|---|---|
| Rent | 15,000 |
| Food | 5,000 |
| Electricity | 3,000 |
| Rent | 15,000 |
| Food | 4,000 |
To find your total rent expenses, enter:
=SUMIF(A2:A6,"Rent",B2:B6)
Result: 30,000
This is useful for preparing monthly budgets and tracking business expenses.
Calculate Sales Greater Than a Specific Amount
You can also use comparison operators as SUMIF conditions.
For example, if you want to add all sales amounts greater than 10,000 in Column B, use:
=SUMIF(B2:B100,">10000")
Excel checks the values in Column B and adds only those greater than 10,000.
Other useful comparison conditions include:
| Condition | Formula Example | Meaning |
|---|---|---|
| Greater than | =SUMIF(B2:B10,">5000") | Adds values above 5,000 |
| Less than | =SUMIF(B2:B10,"<5000") | Adds values below 5,000 |
| Equal to | =SUMIF(B2:B10,5000) | Adds values equal to 5,000 |
| Not equal to | =SUMIF(B2:B10,"<>5000") | Adds values other than 5,000 |
| Greater than or equal to | =SUMIF(B2:B10,">=5000") | Adds values of at least 5,000 |
Use a Cell Reference as the Condition
You don't always need to type the condition directly into the formula. You can also refer to another cell.
For example, suppose cell D2 contains the product name "Laptop".
Use this formula:
=SUMIF(A2:A100,D2,B2:B100)
Excel reads the condition from D2 and calculates the total sales for that product.
This approach is especially useful when creating automated reports. Simply change the product name in D2, and the result updates automatically.
Use SUMIF with Partial Text Matching
Sometimes product names or descriptions contain additional words.
For example, your sales data might include:
Samsung Mobile
Samsung Tablet
Samsung Accessories
Apple Mobile
If you want to calculate the total sales of all products containing the word "Samsung", use:
=SUMIF(A2:A100,"*Samsung*",B2:B100)
The asterisk (*) is a wildcard that represents any number of characters.
You can also use the question mark (?) wildcard to represent a single character.
SUMIF with One Condition Using Different Operators
Excel supports several criteria types in SUMIF. Understanding them helps you create more flexible formulas.
| Requirement | SUMIF Formula |
|---|---|
| Match exact text | =SUMIF(A2:A20,"Laptop",B2:B20) |
| Match a number | =SUMIF(A2:A20,100,B2:B20) |
| Greater than 100 | =SUMIF(A2:A20,">100",B2:B20) |
| Less than 500 | =SUMIF(A2:A20,"<500",B2:B20) |
| Match text beginning with A | =SUMIF(A2:A20,"A*",B2:B20) |
| Match text ending with Pro | =SUMIF(A2:A20,"*Pro",B2:B20) |
| Exclude a specific word | =SUMIF(A2:A20,"<>Laptop",B2:B20) |
These examples can help you handle different reporting requirements without creating complicated formulas.
Common SUMIF Errors and How to Fix Them
Although SUMIF is easy to use, a few common mistakes can produce incorrect results.
Using different range sizes
The criteria range and sum range should normally have matching dimensions. For example, avoid using A2 as the criteria range and B2 as the sum range.
Incorrect spelling in text criteria
If your condition is "Laptop" but the data contains "Laptops", Excel won't treat them as an exact match. Check your spelling or use a wildcard if partial matching is intended.
Forgetting quotation marks around text
Text conditions should be enclosed in double quotation marks.
Correct:
=SUMIF(A2:A10,"Laptop",B2:B10)
Incorrect:
=SUMIF(A2:A10,Laptop,B2:B10)
Using comparison operators incorrectly
When typing a comparison condition directly, put the operator and number inside quotation marks.
Correct:
=SUMIF(B2:B10,">5000")
Numbers stored as text
If your sales amounts are stored as text instead of numbers, Excel may not calculate the expected total. Convert those entries into numeric values before using SUMIF.
SUMIF vs SUM: What's the Difference?
Both functions calculate totals, but they serve different purposes.
| Feature | SUM | SUMIF |
|---|---|---|
| Main purpose | Adds numbers | Adds numbers based on a condition |
| Conditions | Not required | One condition |
| Syntax | =SUM(B2:B10) | =SUMIF(A2:A10,"Laptop",B2:B10) |
| Best use | Calculate a general total | Calculate category-specific totals |
| Example | Total sales of all products | Total sales of laptops only |
Use SUM when you want to add all selected numbers. Choose SUMIF when you only want to include values that match a particular requirement.
When Should You Use SUMIF in Excel?
SUMIF is especially useful when your spreadsheet contains many records and you frequently need totals for individual categories.
For example, a business owner can use it to calculate product-wise revenue, a shop manager can track category-wise expenses, and an accountant can prepare customer payment summaries.
It also works well in dashboards because you can connect the formula to dropdown selections or cell references. This makes it possible to generate reports without repeatedly changing the formula.
One important point: SUMIF supports only one condition. If you need to calculate a total based on two or more conditions, use the SUMIFS function instead.
Final Thoughts
The SUMIF function is one of the most practical Excel formulas for managing sales, expenses, payments, and other numerical records. It helps you calculate specific totals without manually searching through your spreadsheet.
Once you understand the formula structure, you can use text conditions, numerical comparisons, wildcards, and cell references to handle a wide range of everyday tasks.
Start with a simple product-wise sales example, practise changing the criteria, and gradually use SUMIF in your own business reports or financial spreadsheets. It can save time and make your calculations more organised.
Frequently Asked Questions (FAQs)
1. What is the SUMIF function used for in Excel?
SUMIF adds numbers that meet one specified condition, such as calculating total sales for a particular product.
2. Can SUMIF work with text conditions?
Yes. You can use text conditions such as "Laptop", "Mobile", or "Rent" to calculate matching totals.
3. How do I use SUMIF with a cell reference?
Use a cell reference as the criteria argument. For example: =SUMIF(A2:A100,D2,B2:B100).
4. Can SUMIF calculate values greater than a specific number?
Yes. Use a comparison operator, such as =SUMIF(B2:B100,">5000"), to add values greater than 5,000.
5. What is the difference between SUMIF and SUMIFS?
SUMIF calculates totals based on one condition, while SUMIFS can calculate totals based on multiple conditions.

COMMENTS