How to use AVERAGEIF and AVERAGEIFS in Excel

AVERAGEIF with one condition

The sheet is a support team's ticket log: date, agent, channel, category and how many hours each ticket took to resolve. To get the average resolution time for chat tickets, AVERAGEIF tests column C and averages column E: =AVERAGEIF(C2:C11,"Chat",E2:E11).

Typing "Chat" into the formula works, but a cell is better. Put the channel in H1 and point the condition at it, then change H1 to Email or Phone and the result follows. Text conditions are not case-sensitive, so "chat" matches Chat.

The third argument is optional. Leave it out and AVERAGEIF averages the tested range itself, which is how =AVERAGEIF(E2:E11,">0") averages only the positive values in one column.

Conditions with operators: greater than, less than, not equal

Put the operator and the number inside one pair of quotes: ">2", "<=4", "<>0". To compare with a cell, keep the operator in quotes and join the cell with &. This averages only the slow tickets, the ones that took more than the hours in H2.

For "not equal" on text, "<>Email" averages every ticket that did not come in by email. The same idea works on any column: =AVERAGEIF(C2:C11,"<>Email",E2:E11) returns 2.17 hours.

The quote trap: ">H2" compares with the letters H2

Inside quotes, H2 is just text, so Excel looks for hours greater than the word "H2". No number matches, nothing is averaged, and you get #DIV/0! even though there are plenty of slow tickets. The fix is the formula in the previous section: ">"&H2.

Wildcards: average by part of the text

The categories are "Billing - refund", "Billing - invoice" and so on. An asterisk matches any number of characters, so "Billing*" matches every category that starts with Billing. "*refund" matches anything that ends in refund, and "*refund*" anything that contains it. A question mark matches exactly one character.

To search for a real asterisk or question mark, put a tilde in front of it: "~*". Wildcards only work on text; they do nothing on numbers or dates.

AVERAGEIFS: several conditions and a date range

AVERAGEIFS takes the range to average first, then pairs of range and condition. A row counts only if every condition is true. For a date range, use the date column twice: once with ">=" and the start date, once with "<=" and the end date. Here H3 and H4 hold October 1 and October 31.

Without the channel condition, =AVERAGEIFS(E2:E11,A2:A11,">="&H3,A2:A11,"<="&H4) is the October average for every channel: 3.75 hours. Keep the dates in cells or build them with DATE(2026,10,1). A date typed as text inside the condition, such as ">=10/1/2026", depends on your regional settings and breaks when the file is opened elsewhere.

AVERAGEIFS exists in Excel 2007 and later, so it works in every current version.

#DIV/0! when nothing matches

An average is a sum divided by a count. When no row meets the condition, the count is 0 and AVERAGEIF returns #DIV/0!. No tickets came in on Social, so =AVERAGEIF(C2:C11,"Social",E2:E11) shows the error. It is not a bug, but it looks like one on a dashboard.

Wrap the formula in IFERROR and show a message or a blank (""). Do not show 0: a 0 average looks like a real, very fast result.

Blanks are skipped, zeros are not

Maya's phone ticket in row 11 is still open, so its hours cell is empty. AVERAGEIF skips empty cells in the range it averages, so the phone average is 3.75 hours from the two closed tickets. If someone had typed 0 for "not resolved yet", the average would drop to 2.5, because a 0 is a number and gets counted.

Leave unknown values empty, or exclude zeros with a second condition: =AVERAGEIFS(E2:E11,C2:C11,"Phone",E2:E11,"<>0"). Text such as "open" in the hours column is also skipped, but it hides the problem; an empty cell is clearer.

Which one to pick

One condition: AVERAGEIF is fine. Two or more, or a date range: AVERAGEIFS. Many people use AVERAGEIFS even for one condition, because its argument order matches SUMIFS and COUNTIFS and adding a second condition later needs no rewrite.

If you need OR logic, a test on a calculated value, or the result for every group at once, Excel 365 users can use =AVERAGE(FILTER(E2:E11,C2:C11="Chat")) or a PivotTable with the value field set to Average. For skipping zeros specifically, see the guide on averaging without zeros.