Excel IF SUM: How to Combine IF, SUMIF, and SUMIFS Formulas

Formulas & functions · Updated November 18, 2026

To test a total and return one result or another, combine IF with SUM: for example, with amounts in B2:B5, =IF(SUM(B2:B5)>1000,"Target met","Below target") returns “Target met” when the sum exceeds 1,000. If you mean “sum only the values that meet a condition,” use SUMIF for one condition or SUMIFS for multiple conditions.

What “IF SUM” can mean in Excel

“Excel IF SUM” can describe two different tasks. The first is to add a range and then make a decision based on the total. In that case, SUM calculates the number and IF checks it. The second is to add only values that satisfy a rule, such as totaling sales for one region. SUMIF and SUMIFS are designed for that task.

The distinction matters because the formulas answer different questions. IF(SUM(…)) evaluates one overall total. SUMIF and SUMIFS evaluate individual rows against criteria and add the corresponding values. Choosing the right one keeps a workbook easier to read and reduces the chance of including the wrong records.

Use IF with SUM to test a total

Use this approach when the decision depends on the total of a range: whether a target was reached, whether expenses exceeded a limit, or whether a group of scores passed a threshold. With amounts in B2:B5 of 300, 250, 400, and 150, the sum is 1,100. Put this formula in another cell:

With the four amounts in B2:B5, the formula returns “Target met” because their total, 1,100, is greater than 1,000: =IF(SUM(B2:B5)>1000,"Target met","Below target")

IF has three parts: a condition, the value to return if the condition is true, and the value to return if it is false. SUM(B2:B5) supplies a total for the condition to test. The comparison operator is important: > means greater than, >= means greater than or equal to, < means less than, and <> means not equal to.

You can also have IF return numbers, a cell reference, or another formula instead of text. For example, it could return one commission rate when a total meets a threshold and another rate otherwise. Use quotation marks around text results, but not around numbers or references. If you omit IF’s final, false-result argument, Excel returns FALSE when the condition is not met.

Use SUMIF for one condition

SUMIF adds values associated with rows that meet one condition. Its argument order is SUMIF(range, criteria, [sum_range]): range is where Excel checks the condition, criteria is the rule, and sum_range contains the numbers to add. The last argument is optional. If you leave it out, Excel adds the cells in range itself.

Suppose A2:A6 contains East, West, East, North, and West, while B2:B6 contains 120, 80, 200, 50, and 150. To total the amounts for East, use this formula. The matching values are 120 and 200, so the result is 320:

With regions in A2:A6 and sales in B2:B6: =SUMIF(A2:A6,"East",B2:B6)

You can put the criterion in a cell instead of typing it into the formula. That makes it easier to reuse the calculation for different regions. For example, if D2 contains East, Excel uses the value in D2 as the criterion:

With regions in A2:A6, sales in B2:B6, and the requested region in D2: =SUMIF(A2:A6,D2,B2:B6)

SUMIF is useful for totals by product, employee, category, or location when there is only one rule. It is not the same as adding values with a condition attached to the total: it checks each row’s criterion range and sums the corresponding position in the sum range.

Use SUMIFS for multiple conditions

SUMIFS is the usual choice when every included row must satisfy more than one rule. Its argument order differs from SUMIF: the sum range comes first, followed by pairs of criteria ranges and criteria. Each pair adds another condition, and all the conditions must be met for a row to contribute to the total.

For example, imagine A2:A7 contains regions, B2:B7 contains months, and C2:C7 contains sales. The rows are East/January/120, East/February/90, West/January/80, East/January/200, North/January/50, and West/February/150. The following formula totals East sales in January. The matching values are 120 and 200, giving 320:

With regions in A2:A7, months in B2:B7, and sales in C2:C7: =SUMIFS(C2:C7,A2:A7,"East",B2:B7,"January")

For an additional condition, append another criteria-range-and-criteria pair. For instance, a report might require a particular region, month, and product category. SUMIFS is also useful for date windows, such as totaling transactions on or after a start date and before an end date. Keeping the boundaries in worksheet cells makes them simple to change without editing the formula.

SUMIFS is available in current Microsoft 365 versions of Excel. SUMIFS was introduced in Excel 2007, so it is also supported by later desktop releases. If a workbook must open in a much older version, compatibility may be a consideration.

Write criteria for numbers, text, and dates

Criteria can be a value, text, a comparison, or a reference to a cell containing one of those values. A plain text criterion such as East is generally not case-sensitive: Excel does not distinguish East from EAST in SUMIF or SUMIFS matching. For numeric comparisons, put the operator and value together in quotation marks, such as ">100".

When the comparison value is stored in a cell, join the operator to that reference with an ampersand. This lets the criterion change when the cell changes:

With amounts in B2:B20, categories in A2:A20, and the threshold in D2: =SUMIF(B2:B20,">"&D2,A2:A20)

This particular formula adds the values in A2:A20 for rows where the corresponding B value is greater than D2; the sum range and criteria range can be different columns. Check the argument order carefully: the range being tested is first, and the values being added are last. If your goal is to add the B amounts where they exceed D2, use B2:B20 as the sum_range too:

With amounts in B2:B20 and the threshold in D2: =SUMIF(B2:B20,">"&D2,B2:B20)

For text patterns, an asterisk matches any number of characters and a question mark matches one character. A tilde can escape a wildcard when you need to search for a literal asterisk or question mark. For dates, use real Excel date values in cells where possible. A criterion such as ">="&F1 joins the greater-than-or-equal operator to the date in F1; it is safer than relying on a date written in a particular text format.

Decide which formula fits the question

  • IF with SUM: Choose it when you need to calculate one total and return a result based on that total, such as a status or message.
  • SUMIF: Choose it when one condition determines which rows contribute to a total.
  • SUMIFS: Choose it when two or more conditions must all be true for a row to count.
  • Plain SUM: Choose it when every number in the range should be added and no conditional result is needed.

A common misconception is that IF is required whenever a request includes the word “if.” For a conditional total, SUMIF or SUMIFS is usually more direct than nesting IF inside SUM. A formula such as an array of IF tests can be harder to maintain and may behave differently in older Excel versions. Use IF when you need a decision about a calculated result; use the conditional-sum functions when you need to select records to add.

Common errors and ways to check the result

When a formula returns zero or an unexpected total, check the data and the ranges before changing the function. SUM ignores text in referenced cells, so numbers imported or entered as text may not be added as expected. A value that looks like a number is not always stored as a number. Also check for leading or trailing spaces in category names, inconsistent labels, and criteria that do not match the source data.

  • Check the argument order. SUMIF takes the criteria range first and the optional sum range last. SUMIFS takes the sum range first.
  • Match the range sizes. In SUMIF and SUMIFS, the criteria ranges should align row by row with the values being summed. Unequal ranges can produce misleading results or errors.
  • Check comparison syntax. A condition such as greater than 100 needs the operator and value together as a criterion. If the value is in a cell, join the operator and reference with &.
  • Test the matching records. Filter or inspect the source rows that satisfy the criterion, then add a few matching values manually to see whether the expected rows are included.
  • Use absolute references when copying. If copying a formula down should keep the source ranges fixed, select the references and press F4 in Windows to add dollar signs. On Mac, the equivalent shortcut is commonly Command+T, though keyboard settings can affect it.

For large reports, a simple layout helps: keep source data in consistent columns, use one header row, avoid merged cells inside the data, and put changing criteria in clearly labeled cells. Excel Tables can also make expanding datasets easier to manage, but confirm that the formula uses the intended table columns and criteria.

Frequently Asked Questions

Can I put SUM inside IF in Excel?
Yes. Use a formula such as =IF(SUM(B2:B5)>1000,"Yes","No") to test the total of B2:B5. IF checks whether the total satisfies the condition and returns the appropriate result.

What is the difference between SUMIF and SUMIFS?
SUMIF applies one condition, while SUMIFS can apply multiple conditions that must all be satisfied. Their argument order also differs: SUMIF starts with the criteria range; SUMIFS starts with the sum range.

Can SUMIF add values when the condition is in another column?
Yes. Give SUMIF the column to test as its first argument and the column to add as its third argument. The ranges should cover corresponding rows so that each tested record maps to the correct value.

Does SUMIF distinguish uppercase and lowercase text?
No. Standard SUMIF and SUMIFS text criteria are not case-sensitive. If capitalization must determine which rows count, a different approach is needed.

Why does SUMIF return zero when matching values are visible?
Common causes include extra spaces or inconsistent text in the criteria column, numbers stored as text, a criterion that does not match the underlying value, or a sum range that does not line up with the criteria range. Check each item and verify the matching rows directly.

Can I use SUMIFS with dates?
Yes. Dates can be criteria in SUMIFS, including start and end boundaries. Store actual Excel dates in cells and combine comparison operators with those cells when building date criteria; this avoids many date-format mismatches.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.