SUMIF Excel Cell Reference: Syntax, Examples, and Fixes

With criteria in A2:A6 and amounts in C2:C6, enter =SUMIF(A2:A6,"North",C2:C6) to add the amounts for rows marked North; if those amounts are 120, 75, and 40, the result is 235. In SUMIF, the cell references identify where Excel should look for matching criteria and which corresponding values it should total.

What SUMIF cell references mean

SUMIF adds values that meet one condition. A reference such as A2:A6 points to a range of cells, while a reference such as D2 points to one cell. When you write a SUMIF formula, you tell Excel which range to inspect, what condition to apply, and—when the values to add are elsewhere—which range contains those values.

The function has three arguments in this order: the criteria range, the criterion, and the optional sum range. The criteria range holds the values Excel checks. The criterion can be typed into the formula, such as the text North, or supplied by a cell reference. The sum range contains the numbers to add. If you leave out the sum range, Excel adds qualifying numbers from the criteria range itself.

These are separate roles, even when the ranges are on the same worksheet. For example, a region column can be checked while a sales column is totaled. The rows need to correspond: the first region should pair with the first sales amount, the second region with the second amount, and so on. Using ranges of the same size and shape is the clearest way to avoid unexpected results.

How the arguments line up

Imagine a small sales list with regions in A2:A6 and sales amounts in C2:C6. The North entries line up with 120, 75, and 40. The formula in the opening example checks each cell in A2:A6, includes the amount from the matching row in C2:C6 when the region is North, and adds the three included amounts: 120 + 75 + 40 = 235.

If you omit the third argument, SUMIF sums the qualifying cells in the criteria range. With sales amounts in B2:B6 of 85, 120, 100, 60, and 150, a condition of at least 100 adds the third, second, and fifth values. With the amounts in B2:B6, this formula returns 370:

With the sales amounts in B2:B6, use =SUMIF(B2:B6,">=100"). It returns 370 because 120 + 100 + 150 = 370.

When a separate sum range is supplied, Excel uses the position of a match in the criteria range to find the corresponding value in the sum range. For instance, a match in A4 leads Excel to the corresponding row in the supplied sum range. A practical habit is to select ranges with matching row boundaries, such as A2:A100 and C2:C100, rather than mixing a short criteria range with an unrelated or offset sum range.

Use a cell reference for the criterion

Putting the condition in a worksheet cell makes a formula easier to reuse. A user can change the selected region in a small input cell instead of editing the formula. This is useful in summary sheets, dashboards, and reports where the same calculation is repeated for different categories.

With regions in A2:A6, amounts in C2:C6, and the word North in D2, use =SUMIF(A2:A6,D2,C2:C6). The formula returns 235. If D2 changes to South and the South amount in the example data is 80, the same formula returns 80 without needing to be rewritten.

A cell used as the criterion can also hold a number or a date, not just text. For a comparison such as “greater than or equal to” a value in another cell, join the comparison operator to the cell reference. With amounts in A2:A6 and a threshold of 100 in D2, use =SUMIF(A2:A6,">="&D2). It returns 370 for the values 85, 120, 100, 60, and 150. The operator is part of the criterion; D2 supplies the threshold.

Be aware that a typed number and a number stored as text may not behave alike in every workbook. If a criterion looks right but matches nothing, check the source cells and the criterion cell for inconsistent data types, extra spaces, or values that only appear numeric because of their formatting.

Relative, absolute, and mixed references

Excel normally adjusts relative references when a formula is copied. For example, A2:A6 becomes A3:A7 if you copy the formula down one row. That adjustment is often useful for a criterion cell that should move from D2 to D3, but it is usually not wanted for the source data ranges: the list being summarized should remain fixed.

Dollar signs lock a column, a row, or both. In a reference such as $A$2:$A$6, both ends stay fixed when the formula is copied. In A2, neither part is locked; in $A2, the column is fixed but the row can change; in A$2, the row is fixed but the column can change. Pressing F4 while editing or selecting a reference cycles through its relative and absolute forms in Windows Excel. On Mac, the equivalent shortcut is typically Command+T; keyboard mappings can vary with the Mac’s settings and keyboard.

With regions in A2:A6, amounts in C2:C6, and criteria in E2:E3 (North, then South), enter this in F2 and copy it down: =SUMIF($A$2:$A$6,E2,$C$2:$C$6). F2 returns 235, and F3 returns 80. The dollar signs keep both source ranges fixed while E2 changes to E3. If the references were left relative, copying down could shift the source ranges and omit the first row of data.

Absolute references are not automatically better in every situation. If each copied formula should summarize a different block of data, relative references may be intentional. Decide which references should move before copying, then inspect the first and last copied formulas to make sure their ranges still cover the intended rows.

Text criteria, wildcards, and dates

Text criteria can be entered directly in quotation marks or placed in a cell. SUMIF comparisons are not case-sensitive, so a criterion for North also matches NORTH or north. If capitalization distinguishes categories in a dataset, SUMIF alone does not enforce that distinction; the data or the calculation method needs to account for it separately.

For text patterns, an asterisk represents any number of characters, while a question mark represents one character. A tilde makes a following wildcard character literal. With item names in A2:A5 and amounts in B2:B5—Pen 10, Pencil 15, Pen set 25, and Pad 8—use =SUMIF(A2:A5,"Pen*",B2:B5). It returns 50 because Pen, Pencil, and Pen set begin with Pen. The asterisk matches the remaining characters, including none.

Numeric criteria can use operators such as greater than, less than, or not equal to. When the operator is typed directly into a criterion, keep it inside the quotation marks, as in the earlier greater-than-or-equal example. When the comparison value is in a cell, join the operator and reference with the ampersand character. This distinction matters: entering a cell reference next to a quoted operator without joining them does not construct the intended criterion.

Dates are stored as serial numbers in Excel, even when formatted to display as calendar dates. For date-based criteria, make sure the cells contain real Excel dates rather than text that merely looks like a date. A criterion that uses a date stored in a cell can combine a comparison operator with that reference, following the same pattern as a numeric threshold. For reliable results, use a cell formatted as a date and verify that the source date column contains actual date values.

Use SUMIF on another sheet or in a table

SUMIF can refer to ranges on another worksheet. If the sheet name has no spaces, use the sheet name, an exclamation mark, and the range. With regions in Sales!A2:A6, amounts in Sales!C2:C6, and North in Summary!A2, enter this formula on the Summary sheet: =SUMIF(Sales!$A$2:$A$6,A2,Sales!$C$2:$C$6). It returns 235 for the sample sales data. If a sheet name contains spaces, surround the sheet name with apostrophes before the exclamation mark.

Excel tables offer another way to refer to data. Structured references use table and column names instead of fixed cell addresses, and they generally expand as rows are added to the table. With an Excel table named Orders containing columns named Region and Amount, and North in G2, use =SUMIF(Orders[Region],G2,Orders[Amount]). If the table contains the same North amounts of 120, 75, and 40, the result is 235. The table and column names must match the names in the workbook.

Ordinary cell references and structured references solve the same basic problem, but suit different layouts. Fixed ranges are straightforward for a stable list or a one-off calculation. Table references are often easier to maintain when a list grows, because the named columns describe what is being checked and totaled. Whichever style you use, confirm that the criteria and sum ranges refer to corresponding records.

Common SUMIF reference problems

A result of zero does not always mean the formula is broken. It can mean there are no matches, or that values which look alike are stored differently. If SUMIF returns an unexpected result, check these points:

Confirm that the criteria range actually contains the criterion, including spelling and any leading or trailing spaces.
Check that the criterion cell contains the intended value and that a comparison operator is joined to a referenced threshold where required.
Make sure the sum range contains numbers. A number stored as text may not contribute to a sum as expected.
Compare the boundaries of the criteria and sum ranges. They should cover the same records in the same order.
Check whether the formula was copied and its references shifted. Add dollar signs to source ranges that should stay fixed.
For wildcard criteria, verify that an asterisk or question mark is meant to act as a wildcard rather than match a literal character.

A common misconception is that SUMIF can check several independent conditions at once. It is designed for one condition. If the task requires, for example, adding North-region sales only when the sale is also at least 100, use SUMIFS, which supports multiple criteria pairs.

With regions in A2:A7, amounts in C2:C7, and the rows North/120, South/150, North/75, North/140, East/100, and South/80, use =SUMIFS(C2:C7,A2:A7,"North",C2:C7,">=100"). It returns 260: the two North amounts meeting the threshold are 120 and 140. SUMIFS places the sum range first, followed by each criteria range and its criterion, so its argument order differs from SUMIF.

Frequently Asked Questions

Can the SUMIF criterion be a cell reference?
Yes. Put the value to match in a cell and use that cell as the criterion argument. This lets you change the requested category or value without editing the formula.

Why does my SUMIF formula return zero?
Check for unmatched text, extra spaces, text-versus-number differences, and an incorrect criteria range. Also confirm that the criterion uses the correct operator and that the sum range contains numeric values.

Do the SUMIF ranges need to be the same size?
They should be the same size and shape so each qualifying record maps clearly to its corresponding value to sum. Mismatched ranges can make the calculation difficult to audit and may not pair records as you expect.

How do I keep a SUMIF range fixed when copying a formula?
Add dollar signs to the range references, such as $A$2:$A$100. Leave the criterion reference relative if it should move to a different cell as the formula is copied.

Can SUMIF use more than one condition?
No. SUMIF applies a single condition. Use SUMIFS when multiple conditions must all be met, such as matching a region and a minimum sales amount.

Is SUMIF case-sensitive?
No. It treats text with different capitalization as a match. If capitalization must determine which records qualify, use a different approach that explicitly tests case.

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.