Excel WhisperExcel Whisper

How to Use SUMIFS Function in Excel

Complete guide to Excel's powerful multi-criteria sum function with practical examples

SUMIFS is an advanced Excel function that allows you to sum values based on multiple criteria. Whether you need to calculate sales for specific regions and products, or analyze data with complex conditions, SUMIFS makes it easy to get accurate results. Learn through practical examples from basic sums to advanced filtering techniques.

Try SUMIFS Online

Test SUMIFS function directly in your browser - faster and easier than Excel

Pro Mode
Try this

Drop Excel or CSV files here

Upload up to 2 files (10MB total)โ€ข
Free Plan
Upgrade to Standard for higher limits โ†’

SUMIFS Function Guide

Step-by-step instructions for using SUMIFS

Function Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Steps to Use

1

Select the cell where you want the sum result

2

Start with =SUMIFS(

3

Select the range containing values to sum (e.g., D2:D100)

4

Select the first criteria range (e.g., A2:A100)

5

Enter the first criteria (e.g., "Electronics" or ">1000")

6

Add more criteria ranges and conditions as needed (each in pairs)

7

Press Enter to complete the formula

Use Cases

Sales Analysis

Sum sales based on region, product category, and date ranges

Expense Tracking

Calculate total expenses by department and time period

Performance Metrics

Analyze performance data based on multiple conditions

Tips & Notes

  • 1Sum range and all criteria ranges must be the same size (rows and columns)
  • 2Text criteria must be enclosed in double quotes (e.g., "Sales")
  • 3Numeric criteria can use comparison operators (e.g., ">5000", "<=100")
  • 4Date criteria should use the proper format (e.g., ">="&DATE(2023,1,1))
  • 5You can use wildcards (* for any sequence of characters, ? for any single character) in text criteria
  • 6Criteria are combined with AND logic (all conditions must be met)
  • 7Up to 127 criteria pairs (range and condition) can be used
  • 8To implement OR logic, create multiple SUMIFS formulas and add them together

Frequently Asked Questions about SUMIFS

Common questions and solutions for Excel SUMIFS function

SUMIF allows you to sum values based on a single criterion, while SUMIFS lets you apply multiple criteria across different ranges. SUMIFS is more powerful for complex calculations where you need to meet several conditions simultaneously, such as summing sales values that match specific regions, dates, and product categories.

This typically happens due to one of these reasons: 1) One of your criteria is not matching any data (check for typos or formatting issues), 2) Your data contains text that looks like numbers (use VALUE function to convert), 3) There are hidden spaces in your data cells, or 4) Date or number formatting differences between your criteria and data.

SUMIFS uses AND logic by default (all conditions must be met). For OR logic (any condition can be met), you need to create separate SUMIFS formulas and add them together. For example: =SUMIFS(sum_range, criteria_range1, criteria1) + SUMIFS(sum_range, criteria_range2, criteria2) will sum values that meet either condition.

Yes, SUMIFS supports wildcards: * (matches any sequence of characters) and ? (matches any single character). For example, =SUMIFS(sum_range, text_range, "*Smith*") will sum values where the corresponding text contains 'Smith' anywhere in the cell. This is useful for partial text matching.

For date ranges, use comparison operators: =SUMIFS(sum_range, date_range, ">="&start_date, date_range, "<="&end_date). This sums values where dates fall between start_date and end_date (inclusive). Make sure your date criteria are formatted as dates or use DATE function to create date values.

The #VALUE! error usually occurs when: 1) Your criteria is incompatible with the data type in the criteria range, 2) One of your ranges has a different size or shape than the others, or 3) You're trying to use array operations not supported by SUMIFS. Check that all your ranges have the same dimensions and your criteria match the data types you're comparing against.

SUMIFS is not case-sensitive by default. For case-sensitive summing, you can use an auxiliary column with the EXACT function to create TRUE/FALSE flags, then use SUMIFS to sum only where the flags are TRUE. Alternatively, in newer Excel versions, you can use SUMIFS in combination with FILTER and EXACT functions.

SUMIFS treats blank cells as zero values when they're in the sum_range. To exclude blanks, combine SUMIFS with additional criteria checking for non-blank cells: =SUMIFS(sum_range, sum_range, "<>", criteria_range1, criteria1, ...). To exclude zeros, add criteria: =SUMIFS(sum_range, sum_range, "<>0", criteria_range1, criteria1, ...).