Set what to add up and the condition, and the SUMIF formula builds as you type. Add a second condition and it becomes SUMIFS. Operators, dates, wildcards and cell references get their quotes and & signs written for you.
Your formula appears here, e.g. =SUMIF($A$2:$A$100,"East",$C$2:$C$100)
Nothing is uploaded. The formula is built in your browser as you type.
A SUMIF generator writes an Excel SUMIF formula from choices you make in a form: the numbers to add up, the range to test, an operator, and the value to compare with. Take a sales export with the region in column A, the order date in B and the amount in C. The finance lead wants East's sales since 1 January 2026. Two conditions, so the generator writes a SUMIFS:
=SUMIFS($C$2:$C$100,$A$2:$A$100,"East",$B$2:$B$100,">="&DATE(2026,1,1))Most people who search for this already know the rule. What goes wrong is the syntax: where the quotes go, when to use &, how to write a date, which end the sum range goes. The generator writes that part for you.
C2:C100.A2:A100), pick an operator, and type the value. The value can be a number, text, a cell such as F1, or a date typed as 2026-01-31.$ to every range and leaves named ranges and tables alone.The formula is built in your browser. Nothing is uploaded, and there's no sign-up or usage limit.
| You pick | You type | The criterion in the formula |
|---|---|---|
| equals | East | "East" |
| equals | 100 or F1 | 100 or F1 |
| does not equal | East | "<>East" |
| is greater than | 100 | ">100" |
| is at least | F1 | ">="&F1 |
| is less than | 2026-01-31 | "<"&DATE(2026,1,31) |
| contains | red | "*red*" |
| begins with | INV | "INV*" |
| ends with | B2 | "*"&B2 |
| is blank | "" | |
| is not blank | "<>" |
Text gets quotes, and a quote inside the text is doubled, as Excel requires. A bare number or cell goes in without quotes. "Is at most" works like "is at least" with <=.
We pasted the output into Excel for Mac on a four-row test table. "East" added only the East row and skipped Eastern. "East*" picked up both. Equals means the whole cell, so use "begins with" or "contains" when the label has anything after it.
"Contains" wraps your text in asterisks, which SUMIF reads as wildcards: red becomes "*red*". Point it at a cell instead and the asterisks are joined on, as in "*"&F2&"*". In our test that version returned 140, the same as "East*". Matching ignores case, so red, Red and RED all count.
">F1" returns 0Everything inside the quotes is text. ">F1" asks for values greater than the letters F1, no number qualifies, and the formula returns 0 with no error to tell you why. To compare against the value in F1, the operator stays in quotes and the cell is joined on with &:
=SUMIF($C$2:$C$100,">="&F1,$C$2:$C$100)Pick "is at least" and type F1, and the generator writes the criterion as ">="&F1. In our Excel check that returned the same 350 as the typed-in ">=100". Change F1 and the total follows, which is the reason to use a cell at all.
The second trap is argument order. SUMIF puts the sum range last. SUMIFS puts it first, followed by pairs of range and criterion. Turn a SUMIF into a SUMIFS by adding a pair to the end and the formula is wrong, because SUMIFS now reads your test range as the numbers to add. When you add a second condition, the generator rewrites the order and shows a note that the sum range has moved from last to first.
Type dates in the generator as 2026-01-31. It writes them as DATE(2026,1,31), which means the same day in any Excel language. A date typed as text inside the criterion, such as ">=1/2/2026", is read using the computer's date setting, so the same file can mean 2 January in New York and 1 February in London.
To total one month, use two conditions on the same date column: "is at least" 2026-01-01 and "is less than" 2026-02-01.
=SUMIFS($C$2:$C$100,$B$2:$B$100,">="&DATE(2026,1,1),$B$2:$B$100,"<"&DATE(2026,2,1))The end date is the first of the next month on purpose. A date with a time on it, such as an order stamped 31 January at 14:30, is larger than DATE(2026,1,31), so "is at most 31 January" would leave it out.
This is also the answer to a common forum question. =SUMIF(A2:A89,MONTH(A2)=1,B2:B89) returns 0, because SUMIF can't apply a function to each cell of the range it tests. A date range can do the same job.
If you type 1/31/2026 into the generator with "is at least" or "is less than", it goes in as text, and the page warns you and asks for the date as 2026-01-31.
Two conditions on the same column for different values, say region equals East and region equals West, always return 0. No cell holds both. The generator notes that every condition must be true and suggests adding two SUMIFs together:
=SUMIF($A$2:$A$100,"East",$C$2:$C$100)+SUMIF($A$2:$A$100,"West",$C$2:$C$100)With several values, the shorter version is SUMIFS with an array constant, wrapped in SUM. The generator doesn't write this one, so type it yourself:
=SUM(SUMIFS($C$2:$C$100,$A$2:$A$100,{"East","West"}))"Does not equal" has its own surprise. In our Excel test, "<>East" returned 297, and that total included a row whose region cell was empty. Blank cells aren't East, so they count. To leave them out, add a second condition on the same range with "is not blank", which adds "<>".
Wildcards can catch extra rows too: a part code with * or ? in it, used with "equals", can match other codes. The generator flags it; put ~ in front of the character to match it literally.
Each range you test has to match the sum range: the same number of cells and the same starting row. The generator checks both, and when one is off it says what Excel would do with it.
Excel handles a mismatch badly. SUMIFS returns #VALUE!. SUMIF doesn't complain at all: Microsoft documents that it resizes the sum range to the shape of the test range, starting from the sum range's first cell, so C2:C50 tested against A2:A100 quietly adds C2:C100. A different starting row is worse. Test A2:A100 against C3:C101 and every region is paired with the amount one row down, and the total looks perfectly plausible.
Go to Formulas > Math & Trig > SUMIF to open the Function Arguments dialog (the Formula Builder on a Mac), with boxes for Range, Criteria and Sum_range. You still write the criterion yourself, quotes and & included. For one plain text condition, typing =SUMIF(A:A,"East",C:C) straight into the cell is faster than either.
For a total for every region at once, a pivot table beats a column of SUMIFs. The criteria syntax on this page also works in COUNTIF, COUNTIFS, AVERAGEIF and AVERAGEIFS.
SUM(SUMIFS(...)) form above.2026-01-31. 1/31/2026 goes in as text.The SUMIF function and SUMIFS function pages cover each argument with more examples. If you need a label on each row rather than a total, such as "Over target", the IF Formula Generator writes a nested IF and the matching IFS side by side.
SUMIF takes one condition and SUMIFS takes several, all of which must be true for a row to be added. The argument order also differs: SUMIF puts the sum range last, as in SUMIF(A2:A100,"East",C2:C100), while SUMIFS puts it first. With one condition, both return the same total.
Put the operator in quotes and join the cell with an ampersand: =SUMIF(C2:C100,">"&F1). Writing ">F1" inside the quotes compares against the letters F1, so it returns 0 with no error. The same pattern works for "<="&F1 and the other comparisons.
Use SUMIFS with two conditions on the same date column, one for the start and one for the end: =SUMIFS(C2:C100,B2:B100,">="&DATE(2026,1,1),B2:B100,"<"&DATE(2026,2,1)). Using less than the first day of the next month also catches dates on the last day that carry a time. The DATE function reads the same in any regional setting, unlike a date typed as text.
No, SUMIFS adds a row only when every condition is true. Two conditions for different values in the same column, such as East and West, therefore always return 0. Add two SUMIFs together, or wrap SUMIFS in SUM with an array constant: =SUM(SUMIFS(C2:C100,A2:A100,{"East","West"})).
Wrap the text in asterisks, which SUMIF treats as wildcards: =SUMIF(A2:A100,"*red*",C2:C100). For text held in a cell, join the asterisks on with ampersands, as in "*"&F2&"*". The match ignores case, so red, Red and RED all count.
The usual causes are a cell reference inside the quotes (">F1" instead of ">"&F1), numbers or dates stored as text, or a trailing space that stops "East " matching "East". Also check the argument order, since swapping SUMIF's ranges tests the amounts column for your text. Two SUMIFS conditions on the same column for different values also give 0, because both have to be true.
No, SUMIF and SUMIFS add every matching row whether it is visible or not. To total only the rows a filter shows, use SUBTOTAL(109,C2:C100) or AGGREGATE instead. Neither takes a condition, so apply the condition as a filter first.