How to Use COUNTIFS, SUMIFS, AVERAGEIFS in Excel

This guide shows you how to use Excel’s three most powerful conditional functions: COUNTIFS, SUMIFS, and AVERAGEIFS. These let you count, add, or average cells that meet one or more criteria — like counting all sales over $500 in the East region. You’ll learn the syntax, see step-by-step examples, and avoid common pitfalls.

What You’ll Need

• Microsoft Excel 2010 or later (the functions work in all modern versions, including Excel for Microsoft 365).
• A worksheet with data organized in columns, with headers in the first row (e.g., Date, Region, Sales).
• Basic familiarity with cell references (e.g., A1, B2) and ranges (e.g., A2:A100).

Step-by-Step

Understanding the Syntax

All three functions follow a similar pattern. The first argument is the range to evaluate, and then you add pairs of criteria ranges and criteria.

• COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …) — Counts how many cells meet all the conditions.
• SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) — Adds the values in sum_range that meet all conditions.
• AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) — Averages the values in average_range that meet all conditions.

Example Data

For the steps below, imagine a table in columns A–D:

• A1: Date, B1: Region, C1: Product, D1: Sales
• Rows 2–10 contain sample data, e.g., 2025-01-05, East, Widget, 150.

Step 1: Use COUNTIFS to Count Rows That Meet Criteria

1. Click an empty cell where you want the count (e.g., F2).
2. Type =COUNTIFS(.
3. Select the first criteria range. For example, to count sales in the East region, select B2:B10.
4. Type a comma, then the criteria. For text, put it in quotes: "East". So far: =COUNTIFS(B2:B10,"East".
5. Add another pair if needed. To count East region sales over $200, add a comma, select D2:D10, comma, then ">200". The full formula: =COUNTIFS(B2:B10,"East",D2:D10,">200").
6. Press Enter. The result is the number of rows where both conditions are true.

Step 2: Use SUMIFS to Add Values That Meet Criteria

1. Click an empty cell (e.g., F3).
2. Type =SUMIFS(.
3. Select the sum range first — the cells you want to add. For sales, that’s D2:D10.
4. Type a comma, then select the first criteria range (B2:B10), comma, then "East".
5. Add more pairs as needed. To sum East sales over $200: =SUMIFS(D2:D10,B2:B10,"East",D2:D10,">200").
6. Press Enter. The result is the total of all matching sales.

Step 3: Use AVERAGEIFS to Find the Average of Matching Cells

1. Click an empty cell (e.g., F4).
2. Type =AVERAGEIFS(.
3. Select the average range first — the cells to average (D2:D10).
4. Type a comma, then the criteria range(s) and criteria, just like SUMIFS. Example: =AVERAGEIFS(D2:D10,B2:B10,"East",D2:D10,">200").
5. Press Enter. The result is the average sales for East region orders over $200.

Step 4: Use Cell References for Criteria

1. Instead of typing text or numbers inside quotes, you can reference a cell. For example, put “East” in cell G1.
2. Write the criteria as G1 (no quotes). The formula becomes =COUNTIFS(B2:B10,G1).
3. For comparison operators with a cell reference, use the ampersand: ">"&G2 where G2 holds the number 200.

Step 5: Use Wildcards for Partial Matches

1. To match text that starts with a certain string, use the asterisk. For example, "E*" matches “East” and “Eastern”.
2. To match any single character, use a question mark. For example, "W?dget" matches “Widget” but not “Wodget”.
3. Wildcards work in COUNTIFS, SUMIFS, and AVERAGEIFS for text criteria.

Common Mistakes

• Wrong order in SUMIFS/AVERAGEIFS: The sum/average range comes first, then criteria ranges. In COUNTIFS, the criteria range comes first.
• Using quotes around numbers: For numeric criteria, you don’t need quotes unless you’re using an operator. Write 200 or ">200", not "200" (the latter is treated as text).
• Mismatched range sizes: All ranges must have the same number of rows and columns. If one range is A2:A10 and another is B2:B11, you’ll get a #VALUE! error.
• Forgetting the ampersand with cell references: ">"&A1 is correct; ">A1" is not.
• Using AND/OR logic incorrectly: These functions use AND logic — all criteria must be true for a cell to be counted/summed/averaged. For OR logic, you need to add multiple functions together.

Tips

• Use absolute references (e.g., $B$2:$B$10) when copying formulas to keep ranges fixed.
• For dates, use the DATE function or a cell reference: ">="&DATE(2025,1,1).
• Use the Formula Autocomplete: after typing =COUNTIFS(, Excel shows the argument names as you type.
• Test with a small dataset first to verify your logic before applying to large data.
• Combine with SUMPRODUCT for more complex conditions, but only if needed.

Troubleshooting

• #VALUE! error: Check that all ranges have the same dimensions. Also ensure you haven’t mixed up the order of arguments.
• #NAME? error: This usually means you misspelled the function name or used a comma instead of a semicolon (in some locales). Check the function name and separators.
• Result is 0: Verify that your criteria actually match data. Check for extra spaces (use TRIM), or text that looks like numbers but is stored as text. Use the ISNUMBER function to test.
• Criteria with text not matching: Text criteria are case-insensitive but must match exactly except for wildcards. Check for leading/trailing spaces.
• AVERAGEIFS returns #DIV/0!: This means no cells met the criteria. The average of an empty set is undefined. Double-check your criteria or use IFERROR to handle it.

Frequently Asked Questions

Can I use OR logic in COUNTIFS?

Not directly. COUNTIFS requires all conditions to be true (AND logic). For OR, use multiple COUNTIF functions added together, e.g., =COUNTIF(A2:A10,"East")+COUNTIF(A2:A10,"West").

How do I count cells that contain a specific word?

Use wildcards: =COUNTIF(A2:A10,"*word*") counts cells that contain “word” anywhere.

Can I use these functions across different sheets?

Yes, include the sheet name in the range: =SUMIFS(Sheet2!D2:D10, Sheet2!B2:B10, "East").

What is the difference between SUMIF and SUMIFS?

SUMIF has a different argument order (range, criteria, sum_range) and only supports one condition. SUMIFS supports multiple conditions and has the sum_range first. For a single condition, both work, but SUMIFS is more flexible.

How do I use a cell reference for a date criterion?

Reference the cell directly: =COUNTIFS(A2:A10, ">="&E1) where E1 contains a date. No quotes needed around the cell reference.

Why does my formula return 0 even though I see matching data?

Check for hidden spaces, text-formatted numbers, or that your criteria range includes the correct cells. Use the TRIM function or convert text to numbers with VALUE if needed.

Related

• Open Snipping Tool in Windows 10: 8 Ways
• Fix Dropbox Error 0x80070780 Access Issues
• Fix iTunes Error 2 on Windows: Step-by-Step Guide