How to Use SUMIF in Excel with One Condition (Formula & Examples)

Learn how to use the SUMIF function in Excel with one condition. Explore simple formulas, practical examples, text criteria, and common errors.

How to Use SUMIF in Excel with One Condition (Formula & Examples)

   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:

ArgumentDescription
rangeThe cells Excel checks to find the condition.
criteriaThe condition that determines which cells should be included.
sum_rangeThe 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:

ProductSales Amount
Laptop45,000
Mobile20,000
Laptop55,000
Keyboard2,000
Mobile25,000
Laptop35,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 CategoryAmount
Rent15,000
Food5,000
Electricity3,000
Rent15,000
Food4,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:

ConditionFormula ExampleMeaning
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.

RequirementSUMIF 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.

FeatureSUMSUMIF
Main purposeAdds numbersAdds numbers based on a condition
ConditionsNot requiredOne condition
Syntax=SUM(B2:B10)=SUMIF(A2:A10,"Laptop",B2:B10)
Best useCalculate a general totalCalculate category-specific totals
ExampleTotal sales of all productsTotal 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

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content