Grab our practice workbook 👉 HERE and follow along:

What Does SUMIF Do?

Imagine you have a list of weekly grocery spends. You only want to see how much you spent on fruits. SUMIF can help you add up just those numbers.

How to Use SUMIF in Excel

Here’s how you write a SUMIF formula:

=SUMIF(range, criteria, [sum_range])
  • range“: This is the place to look: Where is your data? For example, in which column are your items listed?.
  • criteria“: What to look for: What item or number are you interested in? This could be “Fruits” or any amount like numbers over 50.
  • [sum_range]: Where to add (optional): Where are the numbers you want to add up? If you leave this out, Excel uses the first range you gave it.

Excel SUMIF Example

Suppose you have a list where column B lists items you’ve purchased, and column C lists their costs. Here’s a sample data set:

Excel SUMIF Example

You want to find out how much you spent specifically on fruits. This is the SUMIF formula you’d write:

=SUMIF(B4:B9, "Fruits", C4:C9)
  • B4:B9: this range contains the items purchased.
  • “Fruits”: Excel looks for cells that contain exactly the word “Fruits”. The quotation marks around Fruits signify that it is text.
  • C4:C9: Excel sums the values in column B that correspond to rows where column A has “Fruits”.

SUMIF Greater Than

Logical operators are powerful tools in Excel that allow you to perform calculations based on specific conditions. When combined with SUMIF, logical operators let you sum values meeting certain criteria. For example, amounts greater than or less than a specified number.

Let’s assume you have the same spreadsheet where column B lists items, and column C lists their associated costs. You want to find out the total cost of items where each individual cost is greater than $10.

SUMIF greater than

Here is the SUMIF formula you can use:

=SUMIF(C4:C9, ">10", C4:C9)
  • C4:C9 is the range where Excel checks the condition.
  • “>10” is the condition that selects cells with values greater than 10.
  • C4:C9 is also the range to sum (the same as the condition range, meaning it sums the values that meet the condition).

❗ Ensure that the logical operator and the number are enclosed in quotation marks (e.g., “>10”). If you separate them or leave them out of quotes, Excel will not recognize the condition.

SUMIF greater than
  • SUMIF examines each cell in C4 through C9. Then it adds up the cells that meet the condition of being greater than 10: $15 + $20 = $35

More Examples with Logical Operators

The table below outlines simple syntax examples. You can use them for common tasks.

This way you can use SUMIF with logical operators like ‘greater than’, ‘less than’, or ‘equal to’. You can also sum cells that are ‘blank’ or ‘not blank’.

SUMIF Partial Match with Wildcards

Wildcards are special characters. They help you make Excel formulas like SUMIF more flexible. They’re particularly useful when you need to match patterns rather than exact text. There are three main wildcards used in Excel:

  • *‘ (asterisk): This symbol can stand for any group of letters. For example, “Ap*” would match “Apple”, “Application”, “April”, etc.
  • ?‘ (question mark): Represents any single letter. For example, “Ap?le” would match “Apple” but not “Aple” or “ApplePie”.

Suppose you have a list where column A lists product names, and column B lists sales figures. You want to sum the sales figures for products that start with “Ap“.

Here is the SUMIF Formula Using Wildcards:

=SUMIF(B4:B8, "Ap*", C4:C8)
  • It looks at your list from B4 to B8.
  • It finds any product name that starts with “Ap”.
  • Excel SUMIF adds up the sales amounts from C4 to C8 for these products.
  • Total Sum: Excel SUMIF adds up $15 + $20 + $30 + $25 = $90.

How to Use Excel SUMIF with Dates

Using SUMIF with dates in Excel allows you to sum values based on specific time periods. This can be very useful for tracking expenses, sales, or other metrics over a set timeframe.

Imagine you have a list of sales dates and corresponding sales amounts. You want to sum all sales that occurred before April 2024.

Excel SUMIF with Dates

Here is the SUMIF Formula Using Dates:

=SUMIF(B4:B8, "<2024-04-01", C4:C8)
Excel SUMIF with Dates

💡 If the SUMIF formula does not get you the correct results, it might be that Excel does not recognize the dates.

Excel sees dates as numbers. In such cases, you can combine the Excel SUMIF formula with the Excel DATE function. The DATE function ensures Excel gets the date right, avoiding format confusion.

Here is the updated SUMIF formula:

=SUMIF(B4:B8, "<"&DATE(2024,4,1), C4:C8)

Common Issues

  • Exact Matches: If you type “Food” in your SUMIF function, Excel will look for cells that say exactly “Food.” It won’t count cells that say “Food Expenses.” Use Wildcards when you want to look for partial matches.
  • Check Your Criteria: Make sure you put quotes around words (“Food”) and include symbols directly with numbers (“>10”).
  • Correct Your Sum Range: If the numbers don’t add up right, double-check that your sum range matches the range you’re examining.

Need more flexibility in your Excel SUMIF formulas?

Instead of the standard SUMIF, you can use the newer SUMIFS function. It’s great for applying many filters to your sum range. This can be for specific date ranges or multiple conditions. Check out this detailed article here to learn how to master SUMIFS.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.