COUNTIF, SUMIF, AVERAGEIF and their multi-condition versions, for totals and counts that meet your criteria.
7 articles
AVERAGEIF averages the numbers in rows that meet one condition: =AVERAGEIF(range, criteria, average_range), for example =AVERAGEIF(B2:B9,"Marketing",D2:D9). AVERAGEIFS handles two or more conditions and puts the numbers to average first: =AVERAGEIFS(D2:D9,B2:B9,"Finance",C2:C9,"Senior"). If no row matches, both return the #DIV/0! error, so check your criteria or wrap the formula in IFERROR.
COUNTIFS counts rows that meet several conditions, and SUMIFS adds numbers from those rows. Each condition is a pair: a range, then its criteria. A row counts only if it matches every pair. SUMIFS puts the sum range first: =SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop") totals North laptop revenue.
Excel has three wildcard characters for criteria. An asterisk (*) matches any number of characters, so "CAB-*" means starts with CAB-. A question mark (?) matches exactly one character. A tilde (~) before * or ? makes it a normal character. Use them inside quotes in COUNTIF, SUMIF, COUNTIFS and SUMIFS, for example =COUNTIF(A2:A9,"CAB-*").
COUNTIF counts the cells in a range that meet one condition. Write =COUNTIF(range, criteria), for example =COUNTIF(D2:D9,"Open") to count open tickets. Put text and comparisons in double quotes, like ">=8" or "<>Low". Numbers and cell references go in without quotes. For two or more conditions, use COUNTIFS.
SUMIF adds up the numbers in the rows that meet one condition. Write =SUMIF(range, criteria, sum_range): the cells to check, what they must match, then the numbers to add. For example, =SUMIF(C2:C9,"Travel",D2:D9) totals all Travel claims. Leave out sum_range when the condition is about the numbers themselves.
MAXIFS returns the highest number among the rows that meet one or more conditions, and MINIFS returns the lowest. Both put the numbers first, then range/criteria pairs: =MAXIFS(D2:D9,B2:B9,"Marketing") finds the top Marketing salary. They need Excel 2019 or newer. If nothing matches, they return 0, not an error.
To sum between two dates, give SUMIFS the same date column twice: once with ">="&start and once with "<"&end. For January 2025, write =SUMIFS(D2:D9,A2:A9,">="&DATE(2025,1,1),A2:A9,"<"&DATE(2025,2,1)). Keep the operator in quotes and join the date with &. Using "before the 1st of next month" works for every month.