howto.org.in

How to Use SUMIFS in Excel

Use SUMIFS to add values that meet one or more text, number or date criteria, with correctly sized and locked ranges.

Excel & Google SheetsUpdated 2026-08-08Edited by Pramod TiwariReviewed by Pramod TiwariMicrosoft Excel; verified 8 August 2026
2 min read
Process overview for Use SUMIFS in Excel
A concise overview of the main steps in this guide.

Quick Answer

Use =SUMIFS(sum_range, criteria_range1, criteria1, ...) when you need to add values that meet multiple conditions. All ranges must have matching dimensions, text criteria use quotation marks and cell-reference criteria can be joined to operators with an ampersand.

What You Need

  • Numeric values to total
  • One or more criteria columns
  • Excel worksheet

Safety Precautions

  • Verify totals against a filtered sample.
  • Keep source data types consistent.

Step-by-Step Instructions

  1. Step 1

    Choose the sum range

    Select the numeric cells to add.

  2. Step 2

    Add the first criteria range

    Select the same-sized range to test.

  3. Step 3

    Enter the criterion

    Use text, numbers, dates or a cell reference.

  4. Step 4

    Add more criteria

    Repeat criteria range and criterion pairs as needed.

  5. Step 5

    Lock ranges

    Use absolute references before copying.

  6. Step 6

    Validate the result

    Filter the data manually and compare a sample total.

Practical Example

=SUMIFS($D$2:$D$100,$A$2:$A$100,"East",$C$2:$C$100,">=1000") totals qualifying East-region values.

Common Mistakes

  • Using mismatched range sizes
  • Putting criteria before the sum range
  • Treating numeric text as numbers

Troubleshooting

The result is zero

Check data types, spelling, spaces and criteria syntax.

Dates do not match

Use real Excel dates and construct operator criteria correctly.

Sources and Review

Reviewed by Pramod Tiwari, Editor; official-source verification on 2026-08-08.

FAQs

What is the difference between SUMIF and SUMIFS?

SUMIFS supports multiple criteria and uses sum_range first.

Can criteria refer to cells?

Yes, including operators joined with an ampersand.

Can it use wildcards?

Text criteria can use supported wildcard characters.

Help us improve this guide

Tell us about an outdated instruction, unclear step, broken link or safety concern. Do not include passwords, account numbers, medical records or other sensitive information.

Email a correction or suggestion