PlayInfoBox

Home /Utilities /How to Use SUMIF in Excel to Sum Values That Meet a Condition

How to Use SUMIF in Excel to Sum Values That Meet a Condition

Tap through each step to see exactly how a SUMIF formula comes together.

  1. Click the cell where the result should appear

    Click an empty cell where you want the conditional total to be displayed.

  2. Type the SUMIF formula

    Enter a formula in the form "=SUMIF(range, criteria, sum_range)".

  3. Set the range to check the condition against

    Choose the range of cells that holds the values you want to compare against your condition, such as a column of department names.

  4. Set the range to add up

    Choose the range of cells that holds the actual values to total when the condition is met, such as a column of amounts.

  5. Why it is worth using

    When you need to total only the items that match a condition -- total spending for one department, total sales for one month -- SUMIF calculates it instantly without scanning the table by hand.

When you only need to add up items that meet one condition

Totaling spending for a specific department, or sales for a specific month, normally means filtering or scanning a table by eye first. SUMIF skips that step entirely: give it a range to check, a condition, and a range to sum, and it returns the total for exactly the rows that match, instantly and without touching the rest of the data.

Need more than one condition? That is what SUMIFS is for

SUMIF only evaluates a single condition at a time. The moment you need two or more -- say, a department and a month -- switch to its close relative SUMIFS, which takes a sum range followed by as many condition-range and condition pairs as needed: "=SUMIFS(sum_range, range1, criteria1, range2, criteria2)". Learning SUMIF first makes SUMIFS feel like a natural extension rather than a new function to memorize.

Frequently Asked Questions

What if I need to match more than one condition?

SUMIF only supports a single condition. For two or more conditions, use the SUMIFS function instead, in the form "=SUMIFS(sum_range, range1, criteria1, range2, criteria2)".

Do the condition range and the sum range need to be the same size?

Yes -- both ranges need to cover the same number of rows so each condition cell lines up with the correct value to sum. Mismatched range sizes can produce results that look plausible but are actually wrong.