In Excel: use =AVERAGEIF(range, criteria, [average_range]) — it averages the rows matching one condition, so =AVERAGEIF(A2:A500, "West", D2:D500) gives the mean for the West only.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
range | required | The cells to test against the criterion. |
criteria | required | The condition, such as "West" or ">=100". |
average_range | optional | The cells to average. Omit it to average the tested range itself. |
Related functions
Select the result cell and type =AVERAGEIF(.
Select the column holding the condition values, then a comma.
Type the criterion in quotes — text, or a comparison such as ">100".
If the numbers to average live in a different column, add a comma and select it. Press Enter.
What this does
AVERAGEIF averages the values that satisfy a single condition. Its third argument is optional: leave it out and Excel averages the same range it tested, which is what you want for "average of the values over 100". The argument order is the reverse of AVERAGEIFS — condition range first here, values-to-average first there — and mixing them up is the most common error with the pair. Blank cells in the average range are skipped rather than counted as zero, which keeps the mean honest. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “use averageif in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Regions in A2:A500 and order values in D2:D500: =AVERAGEIF(A2:A500, "West", D2:D500) returns the average order value in the West. Averaging only the values above 100 in a single column, =AVERAGEIF(D2:D500, ">100"), needs no third argument at all. AVERAGEIF is the one-condition workhorse for segment averages, and knowing its argument order differs from AVERAGEIFS saves the classic wrong-column result. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “use averageif in excel”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
Common mistakes
- Swapping the argument order with
AVERAGEIFS, which silently averages the wrong column or errors. - Assuming blanks count as zero — they are excluded, which is usually right but changes the answer.
- Needing two conditions and staying with
AVERAGEIF;AVERAGEIFShandles multiple criteria.
Frequently asked questions
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and puts the average range last; AVERAGEIFS takes many and puts it first. Use AVERAGEIFS whenever there is more than one condition.
Does AVERAGEIF count blank cells as zero?
No, blanks are ignored. If you need them treated as zeros, replace them with zeros first or use SUMIF divided by a COUNT that includes them.
How do I average cells greater than a value?
=AVERAGEIF(D2:D500, ">100") — with the condition on the same column being averaged, the third argument is unnecessary.