Key Take-Aways

- Use the plural functions COUNTIFS, SUMIFS, and AVERAGEIFS to test one or more criteria in the same formula.

- COUNTIFS takes pairs of criteria ranges and criteria and returns a count. SUMIFS and AVERAGEIFS start with the range to sum or average, then accept the same criteria pairs.

- When using comparison operators like greater than or less than, wrap the operator and number in quotes, or concatenate with a cell reference like ">" & A1.

- All criteria ranges used in one COUNTIFS/SUMIFS/AVERAGEIFS must be the same size and shape.

- These functions work with text, numbers, dates, and wildcards. Use absolute references to lock ranges when copying formulas.

## Why use COUNTIFS, SUMIFS and AVERAGEIFS

COUNTIFS, SUMIFS, and AVERAGEIFS let you evaluate data across multiple conditions without extra helper columns. Instead of filtering and manually totaling rows, you build a single formula that returns the result you need. They are the workhorses for dynamic dashboards and conditional calculations.

## The core idea and when to use each function

- COUNTIFS: Count rows that meet all specified conditions.

- SUMIFS: Sum values in a target range where all conditions are met.

- AVERAGEIFS: Average values in a target range where all conditions are met.

Note: The pluralized versions accept multiple criteria. You can use them with just one condition, but the plural forms are more flexible and become your go-to functions.

## Syntax explained

COUNTIFS:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

SUMIFS:

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

AVERAGEIFS:

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Important details:

- For COUNTIFS you list the criteria ranges and criteria pairs only.

- For SUMIFS and AVERAGEIFS you must put the range you want to sum or average first, then the criteria pairs.

- Each criteria pair consists of a range and a matching criterion. You can include as many pairs as you need.

- All criteria ranges must be the same size. If one is A2:A100 then the others must also be 99 rows tall.

## Common criteria types and how to write them

- Exact match text: "Google" or a cell reference like A1 (no quotes if referencing a cell).

- Numeric comparison: ">200" must be in quotes.

- Numeric comparison using a cell value: ">" & B1. This concatenates the operator with the cell value.

- Wildcards for partial text: "*search*" matches any text containing search. "?" matches a single character.

- Dates: treat dates like numbers. Use ">" & DATE(2023,1,1) or ">" & $E$1 where E1 contains a date.

## Step-by-step examples

Example data situation: Columns

- Column A: Month

- Column B: Tactic (Search or Display)

- Column C: Network (Google, MSN, etc.)

- Column D: Clicks

1) COUNTIFS example

Goal: Count rows where Tactic is Search and Clicks are greater than 200.

Formula pattern:

COUNTIFS(B2:B13, "Search", D2:D13, ">200")

Notes:

- The criteria pair B2:B13 and "Search" checks the tactic.

- The pair D2:D13 and ">200" checks numeric clicks. Yes, include the numeric comparison in quotes.

- Result in the sample was 3 because three rows matched both conditions.

2) SUMIFS example

Goal: Sum clicks where Month is February and Tactic is Display.

Formula pattern:

SUMIFS(D2:D13, A2:A13, "February", B2:B13, "Display")

Notes:

- sum_range D2:D13 goes first. Then the criteria pairs follow.

- SUMIFS adds clicks only for rows that match both Month and Tactic.

3) AVERAGEIFS example

Goal: Average clicks in January on network MSN.

Formula pattern:

AVERAGEIFS(D2:D13, A2:A13, "January", C2:C13, "MSN")

Notes:

- average_range D2:D13 comes first.

- AVERAGEIFS ignores rows that do not meet all criteria.

## Practical tips for building robust formulas

- Use cell references for criteria so you can change filters without editing formulas. Example: COUNTIFS(B:B, $F$1, D:D, ">" & $G$1) where F1 contains tactic and G1 contains the numeric threshold.

- Anchor ranges with $ when you plan to copy formulas across cells.

- Keep ranges consistent. If you use entire columns, be consistent: COUNTIFS($B:$B, $F$1, $D:$D, ">" & $G$1).

- If your data has headers, start ranges at the first data row, or use structured tables for clarity.

- For performance with large data sets, reference exact ranges or use Excel tables instead of whole-column references.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Numeric comparisons must be quoted when used directly, e.g. ">200". This is a common gotcha. If you need to reference a cell, use ">" & A1.

- SUMIFS and AVERAGEIFS require the sum/average range first. If you forget this, Excel will return an error or wrong result.

- Make sure criteria ranges are the same shape and size. Mismatched ranges will cause a #VALUE error.

- COUNTIFS, SUMIFS, and AVERAGEIFS are case-insensitive. If you need case sensitivity, use more advanced formulas with SUMPRODUCT or helper columns.

- Wildcards are supported for text criteria. Use * for any number of characters and ? for a single character.

## FAQ

Q: What is the difference between COUNTIF and COUNTIFS?

A: COUNTIF accepts a single criteria range and a single criterion. COUNTIFS supports multiple criteria ranges and criteria pairs. COUNTIFS is more flexible and recommended even for single criteria.

Q: Why do I need quotes around a comparison like ">200"?

A: Excel requires the comparison operator and the number to be a string when typed directly inside the formula. If you use a cell reference for the number, concatenate the operator, for example ">" & $A$1.

Q: Can I use wildcards with COUNTIFS and SUMIFS?

A: Yes. Use * to match any sequence of characters and ? to match one character. For example, COUNTIFS(B2:B100, "*search*") counts rows where the text contains search.

Q: Do SUMIFS and AVERAGEIFS require the same order as COUNTIFS?

A: SUMIFS and AVERAGEIFS require the sum or average range first, followed by the criteria range/criteria pairs. COUNTIFS does not have the leading sum/average range.

Q: What happens if my criteria ranges are different sizes?

A: Excel will return a #VALUE error. Always ensure every criteria range has the exact same number of rows.

Q: Can I use dates in these functions?

A: Yes. Dates work as numeric values. Use comparisons like ">" & DATE(2023,1,1) or reference a cell with a date and concatenate the operator.

## Summary of Key Points / Take-Home Messages

- Prefer COUNTIFS, SUMIFS, and AVERAGEIFS for multi-criteria calculations. They handle one or many conditions in a single formula.

- SUMIFS and AVERAGEIFS require the target range first. COUNTIFS does not.

- Put comparison operators and numbers in quotes or concatenate with cell references.

- Keep criteria ranges the same size, use absolute references when needed, and use tables for better readability and performance.

- Practice by writing a few formulas on real data. Once you build a few, the syntax becomes natural and you can quickly build dynamic dashboards.