Skip to content
en
Practical GuidesAll tools

Categories

Developer ToolsEncoding & DecodingJSON & DataExcel ToolsHash & ChecksumText ToolsDate & TimeNetwork ToolsFile ToolsMarkdown & Diagrams

Excel SUMIFS Formula Generator

Pick the range to add up, add up to four conditions and get a SUMIFS with correctly quoted criteria, ready to paste.

Processed locally in your browser

Options

A cell (B2), a number, text (quoted for you), a date like 2024-01-31, or a formula starting with "=".

Leave the range empty to skip this row.

Leave the range empty to skip this row.

Leave the range empty to skip this row.

Excel uses ";" when the regional settings use a decimal comma. Decimal numbers are then written with a comma too.

What is Excel SUMIFS Formula Generator?

SUMIFS adds the numbers in one range for the rows where every condition is true, for example the sales of the East region above 100. The tricky part is the criteria: operators go inside quotes (">=100"), text needs quotes, comparisons with a cell need the & operator (">="&B1) and dates are safest with DATE().

This generator takes the sum range and up to four rows of criteria range, condition and value. Conditions include equals, not equal, greater and less than, contains, does not contain, begins with, ends with, is blank and is not blank. It writes the criteria correctly, escapes wildcard characters, turns 2024-01-31 into DATE(2024,1,31), warns when ranges have different heights, and shows a SUMIF version for a single criterion and a SUMPRODUCT version that works in any Excel.

How does it work?

  1. Enter the sum range, for example D2:D100, the numbers you want to total.
  2. For each condition give the range to test, choose the condition and type the value: text, a number, a date like 2024-01-31 or a cell such as F1.
  3. Leave unused criteria rows without a range; they are skipped. Choose the separator and the function language for your Excel.
  4. Copy the SUMIFS formula, or one of the equivalents, and read the explanation and warnings under it.

Common use cases

  • Totalling sales for one region and a minimum order value.
  • Adding up expenses between two dates, using a greater-than-or-equal and a less-than-or-equal criterion on the same date column.
  • Summing amounts whose description contains a keyword, with wildcard criteria written for you.
  • Converting a single-criterion SUMIFS to SUMIF, or a SUMIFS to SUMPRODUCT for a workbook opened in an old Excel.

Examples

Pick the range to add up, add up to four conditions and get a SUMIFS with correctly quoted criteria, ready to paste.

Privacy

Excel SUMIFS Formula Generator runs entirely in your browser. The text or files you provide are processed on your device and are not uploaded, logged or stored on our servers.

Limitations

The generator does not read your data: it cannot check that the ranges line up with your columns. All ranges must have the same size, and SUMIFS criteria combine with AND only, so OR needs two SUMIFS added together.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and puts the sum range last: SUMIF(range, criteria, sum_range). SUMIFS takes the sum range first and then any number of range and criteria pairs: SUMIFS(sum_range, range1, criteria1, …). The argument order is a common source of mistakes when switching between them.

How do I sum between two dates?

Add two rows on the same date column: greater than or equal to the start date and less than or equal to the end date. The generator writes them as ">="&DATE(2024,1,1) and "<="&DATE(2024,12,31), which do not depend on the regional date format of the computer.

Why does my SUMIFS return 0 or #VALUE!?

#VALUE! means the ranges have different heights, which the tool warns about. A 0 usually means nothing matched: numbers stored as text, extra spaces in the data, or a text criterion that differs slightly. Remember that ? and * in an equals criterion act as wildcards.

Related tools

More tools in Excel Tools →