You need to sum or count data in Excel based on multiple criteria, but basic functions like SUMIF or COUNTIF only handle one condition. The SUMIFS and COUNTIFS functions are designed for this exact multi-condition analysis. This article explains the syntax for both functions and provides step-by-step instructions for building formulas. You will learn how to aggregate data using several criteria at once.
Key Takeaways: SUMIFS and COUNTIFS Functions
- SUMIFS(sum_range, criteria_range1, criteria1, …): Adds numbers in the sum_range only when all corresponding criteria are met.
- COUNTIFS(criteria_range1, criteria1, …): Counts the number of cells across multiple ranges that meet all specified conditions.
- Using operators like >, <, <>, and wildcards: Enables complex criteria such as “greater than 100” or text containing specific characters.
Understanding SUMIFS and COUNTIFS Syntax
The SUMIFS and COUNTIFS functions are part of Excel’s “IFS” family, which allows multiple criteria. Their structure is logical but requires precise order. The SUMIFS function sums a range of numbers based on one or more conditions. Its first argument is the range you want to sum. Every condition after that requires two arguments: the range to check and the specific criterion to apply to that range.
The COUNTIFS function works similarly but counts cells instead of summing them. It does not have a separate “sum_range”. You only provide pairs of criteria ranges and criteria. Both functions require all conditions to be true for a row or entry to be included. This is a logical AND operation. You can use up to 127 range and criteria pairs in a single formula.
Argument Order and Requirements
For SUMIFS, the order is critical: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). The sum_range and each criteria_range must be the same size and shape, typically a single column. For COUNTIFS, the syntax is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …). All ranges must be the same size. Text criteria must be enclosed in double quotes, while cell references and numbers do not need quotes.
Building a Basic SUMIFS Formula
Follow these steps to create a formula that sums values based on two conditions, such as sales for a specific region and product.
- Identify your data ranges
Ensure your data is in a table or contiguous range. Identify the column with values to sum, the column for your first condition, and the column for your second condition. - Start the SUMIFS formula
Click an empty cell and type =SUMIFS(. First, select or type the range containing the numbers you want to add, such as D2:D100. Type a comma. - Add the first criteria pair
Select the range for your first condition, like B2:B100 for a “Region” column. Type a comma. Then, specify the criterion. To match text in cell F1, type “&”&F1 or type the text directly in quotes: “East”. - Add the second criteria pair
Type a comma. Select the range for your second condition, like C2:C100 for a “Product” column. Type a comma. Specify the criterion, such as “&”&G1 for a product name in cell G1. - Complete and test the formula
Type a closing parenthesis ) and press Enter. The formula will calculate the total sum where both conditions are true. Test by changing the criteria in cells F1 or G1.
Building a Basic COUNTIFS Formula
Use these steps to count rows that meet multiple conditions, such as counting orders above a certain value in a specific month.
- Start the COUNTIFS formula
Click an empty cell and type =COUNTIFS(. There is no sum_range argument. The first argument is the first criteria range. - Define the first condition
Select the range for your first condition, like A2:A100 for “Date”. Type a comma. To count dates in January 2024, use a criterion like “>&=”&DATE(2024,1,1) and “<“&DATE(2024,2,1). - Add a second numeric condition
Type a comma. Select the range for a second condition, like D2:D100 for “Order Value”. Type a comma. To count values over 500, use the criterion “>&500”. - Close the formula
Type a closing parenthesis ) and press Enter. The cell will display the count of rows where the date is in January 2024 and the order value exceeds 500.
Using Operators and Wildcards in Criteria
You are not limited to exact matches. You can use comparison operators and wildcards for partial text matches.
- Apply comparison operators
For numeric conditions, use operators like >, <, >=, <=, and <> (not equal to). The operator and value must be enclosed in quotes. Example: To sum values where quantity is greater than 10, use “>&10” as the criterion. - Use wildcards for text
For partial text matches, use the asterisk (*) to represent any number of characters and the question mark (?) for a single character. To count cells in a “Name” range that start with “Jo”, use “Jo*” as the criterion. - Combine operators and cell references
To make a criterion dynamic, combine an operator with a cell reference using the ampersand (&). For example, to sum values less than the number in cell H1, use “<“&H1 as the criterion.
Common Mistakes and Formula Errors
Formulas may return zero, an error, or an incorrect result due to a few typical issues.
SUMIFS Returns 0 or #VALUE! Error
A result of zero often means no cells meet all criteria. Check for typos in text criteria or mismatched data types. The #VALUE! error usually occurs when the sum_range and criteria ranges are different sizes. Ensure every range in the formula has the same number of rows.
Criteria Not Working With Dates or Numbers
If a date criterion fails, the date in your data might be stored as text, or your criterion might be in the wrong format. Use the DATE function to build the criterion or ensure the data range is formatted as a date. For numbers, avoid having numbers stored as text in the criteria range.
Incorrect Use of Wildcards
Wildcards only work with text criteria. If you use a wildcard on a range formatted as numbers or dates, it will not work. Also, if you need to match a literal asterisk or question mark, precede it with a tilde (~), as in “~*” to find an asterisk character.
SUMIFS vs. COUNTIFS: Core Differences
| Item | SUMIFS | COUNTIFS |
|---|---|---|
| Primary Function | Adds numerical values | Counts cells that meet criteria |
| First Argument | sum_range (required) | criteria_range1 (no sum_range) |
| Result Type | A numerical sum | An integer count |
| Use Case Example | Total sales for Product A in Region East | Number of invoices over $1000 in Q1 |
| Handling Blank Cells | Ignores blanks in sum_range | Can count blanks as a criterion using “” |
You can now use SUMIFS to calculate totals and COUNTIFS to get counts based on multiple filters. Practice by applying these functions to your own sales or inventory data. For more complex logic involving OR conditions, explore combining multiple SUMIFS or COUNTIFS formulas with a plus sign (+). Use the F4 key to toggle absolute references when copying formulas with fixed criteria ranges.