Excel Tutorials
Three functions that answer the three questions you ask of any list: how many, how much, and what is typical. They share one argument pattern, so learning them together takes barely longer than learning one. This lesson covers the syntax, every form the criteria argument can take, and the plural versions that handle more than one condition, using the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
All three are conditional versions of functions you already know. SUM adds everything; SUMIF adds only the rows meeting a condition. COUNT counts everything; COUNTIF counts only matches. AVERAGE averages everything; AVERAGEIF averages the subset. The condition is what turns a raw list into an answer.
They matter because most real questions are conditional. Not what did we spend, but what did we spend on catering. Not how many attendees, but how many from the Northeast. Without these functions the usual workaround is filtering the list and reading a number off the status bar, which is fine once and useless in a report that has to update itself.
The three share an argument pattern, which is the reason to learn them as a set: the range you test comes first, the criteria second, and the range you do maths on comes last. COUNTIF drops that third argument because counting needs nothing to count up. Get the pattern once and all three are yours.
Step-by-step, matching the video above.
All three do conditional aggregation: they look down a range, find the rows matching a condition, and then total, count or average them. Learn the pattern once and you have all three, because the arguments follow the same shape.
Go to Formulas > Function Library. SUMIF lives under Math & Trig, while COUNTIF and AVERAGEIF are under More Functions > Statistical. The fx button beside the formula bar opens the Insert Function dialog with a searchable list and a guided arguments box, which is useful while you are learning the order. Typing =SUMIF( straight into a cell is faster once you know it, and Excel shows the argument tooltip as you go.
=COUNTIF(range, criteria). Two arguments only: the range to look through, and what you are looking for. =COUNTIF(B2:B50,"Gold") counts how many cells in that column say Gold.
=SUMIF(range, criteria, [sum_range]). The first range is the one you test, and sum_range is the one you actually add. In =SUMIF(B2:B50,"Gold",D2:D50), Excel checks column B for Gold and totals the matching rows from column D.
=SUMIF(D2:D50,">100") adds every value in D that is over 100. Because the test and the total are the same column, the third argument is unnecessary.
=AVERAGEIF(range, criteria, [average_range]) follows the identical pattern. =AVERAGEIF(B2:B50,"Gold",D2:D50) gives the average spend of Gold-tier attendees rather than their total.
Text criteria need them: "Gold". So does any comparison: ">100", "<=50", "<>Silver". A bare number like 100 does not need quotes. This is the single most common syntax error with these three functions.
Instead of hard-coding "Gold", put it in a cell and reference it: =SUMIF(B2:B50,F1,D2:D50). Change F1 and every total updates. To combine an operator with a cell, join them with an ampersand outside the quotes: ">"&F1.
The tested range and the sum or average range must line up row for row. SUMIF(B2:B50,"Gold",D2:D40) is a silent bug, since the ranges start together but end ten rows apart, and the result will simply be wrong rather than throwing an error.
The criteria is where nearly all the difficulty lives. These are the forms it can take.
"Gold" matches cells containing exactly that word. Matching is not case sensitive, so gold, Gold and GOLD are all counted together.
100 works with or without quotes and matches cells equal to 100. Be aware that numbers stored as text will not match a numeric criteria, which is a frequent cause of a zero result on imported data.
">100", "<50", ">=0", "<>Silver". The operator and the value go inside one set of quotes together. <> means not equal to.
F1 on its own, with no quotes. To attach an operator, concatenate: ">"&F1. Forgetting the ampersand and writing ">F1" makes Excel look for the literal text >F1, which matches nothing.
"North*" matches anything starting with North. "*conference*" matches anything containing that word. "Q?" matches Q1 through Q9. Precede a wildcard with a tilde, as in "~*", to search for the character itself. Wildcards only work on text, never on numbers.
Wrap the function around the value: ">="&DATE(2026,1,1) is far more reliable than typing a date as text, which depends on your regional settings and often silently fails.
"" counts genuinely empty cells and "<>" counts everything that is not empty. COUNTBLANK and COUNTA do the same jobs more directly.
| Formula | What It Asks | Result |
|---|---|---|
| =COUNTIF(B2:B6,"Gold") | How many Gold attendees? | 3 |
| =SUMIF(B2:B6,"Gold",D2:D6) | Total Gold spend | 1,450 |
| =AVERAGEIF(B2:B6,"Gold",D2:D6) | Average Gold spend | 483.33 |
| =SUMIF(D2:D6,">500") | Total of all spend over 500 | 1,200 |
| =COUNTIF(A2:A6,"North*") | Attendees from any North region | 2 |
| =SUMIF(B2:B6,"<>Gold",D2:D6) | Total spend from everyone else | 760 |
| =AVERAGEIF(B2:B6,"Bronze",D2:D6) | Average Bronze spend, none exist | #DIV/0! |
Row two and row three are the same question asked two ways, and together they are the reason to learn all three at once: the total tells you Gold is your biggest segment, the average tells you whether that is because they spend more or simply because there are more of them. The last row is worth committing to memory, since AVERAGEIF returns #DIV/0! rather than zero when nothing matches, because averaging nothing is undefined. Wrap it in IFERROR if that will ever be on screen for someone else.
The plural versions take multiple conditions, and change the argument order while they are at it.
=SUMIFS(sum_range, criteria_range1, criteria1, ...). The range you are adding now comes first, the opposite of SUMIF. This catches out almost everyone the first time and produces either a wrong number or a #VALUE! error.
=SUMIFS(D:D,B:B,"Gold",A:A,"Northeast") totals rows that are Gold and Northeast. Every condition must be true. For OR logic, add two SUMIFS together.
=COUNTIFS(criteria_range1, criteria1, ...) is just pairs of range and criteria, since counting needs nothing else. It is the one plural function whose order does not surprise you.
Many people write SUMIFS exclusively, because a single consistent argument order is easier than remembering two, and adding a second condition later means no rewrite.
SUMIF cannot do it with one condition, but SUMIFS can point at the same column twice: =SUMIFS(D:D,D:D,">=100",D:D,"<=500").
Three functions, three different questions about the same data.
How many orders are still open, how many attendees chose the vegetarian option, how many rows contain an error. It is also the standard way to find duplicates: =COUNTIF(A:A,A2)>1 returns TRUE for any value appearing more than once.
Total revenue by region, total hours by project, total spend by category. This is the workhorse behind most simple summary tables, and it is what a PivotTable does under the hood when you drop a field into Values.
Average order value per tier, average days to close per owner. Totals can be misleading when group sizes differ wildly, and the average is what makes those groups comparable.
A summary block with count, total and average per category, each pulling from the same data with a different function, tells you far more than any one of them alone and takes about a minute to build.
If the tested range and the sum range are different heights, Excel returns a wrong answer without any error. SUMIF(B2:B50,"Gold",D2:D40) looks fine and is silently broken. Keep both ranges identical.
Writing =SUMIF(D:D,>100) gives a syntax error. The operator and value belong together inside quotes: ">100".
">F1" makes Excel search for the literal text >F1 and match nothing. Concatenate instead: ">"&F1.
SUMIF puts the tested range first, SUMIFS puts the summed range first. Swapping them gives either a #VALUE! error or, worse, a plausible-looking wrong number.
It returns #DIV/0!, because there is nothing to divide by. Wrap it in IFERROR if the cell will be visible on a report.
A criteria of >100 will not match values that are text rather than numbers, so the result comes back as zero on imported data. Run the column through Text to Columns and click Finish to convert it first.
It is not. Gold, gold and GOLD are all counted as the same thing. If you need case-sensitive matching, you need SUMPRODUCT with EXACT.
Asterisks and question marks only work against text values. A criteria like "1*" will not match the number 100.
These functions cannot read from a workbook that is not open and will return #VALUE! until you open the source file. This surprises people who use them for cross-file summaries.
A conditional total is a great formula. It is a worse answer than a number that was never a formula.
Free 14-day trial. No credit card required.
SUMIF handles a single condition and takes the range to test first. SUMIFS handles multiple conditions and takes the range to add first, so the argument order is reversed between the two.
SUMIF(range, criteria, [sum_range]). The range is what gets tested, the criteria is what you are testing for, and sum_range is the column actually added up. If sum_range is left out, Excel adds the range itself.
COUNTIF(range, criteria). It only takes two arguments because it counts matching cells rather than totalling a separate column, so there is no third range to supply.
AVERAGEIF(range, criteria, [average_range]). It works the same way as SUMIF, testing the first range and averaging the third one if you provide it.
Text and any criteria containing a comparison operator must be in quotes, such as "Gold" or ">100". A plain number like 100 does not need them, though it still works if you add them.
Refer to the cell directly, as in COUNTIF(B:B,E1). If you need an operator with it, join them with an ampersand outside the quotes, like COUNTIF(B:B,">"&E1).
No cells matched your criteria, so there was nothing to average. Excel cannot divide by zero matches. Wrap it in IFERROR to display a friendlier message instead.
Yes, with text values. An asterisk matches any number of characters and a question mark matches exactly one, so "North*" counts every entry beginning with North. Wildcards do not work on numbers.
No. All three functions ignore capitalisation, so "gold" and "Gold" are treated as the same value. Matching by case requires SUMPRODUCT with EXACT instead.
Use "" as the criteria to count truly empty cells and "<>" to count everything that is not empty. COUNTBLANK and COUNTA are the more direct alternatives.
The most common cause is a sum_range that is a different size or starting row than the range being tested. Both need to line up row for row, ideally as identical-height references.
Yes. Reference it as normal, such as SUMIF(Data!B:B,"Gold",Data!D:D). They work across sheets but will return an error if the source workbook is closed.
SUMIF only takes one condition, so use SUMIFS with two: SUMIFS(D:D,D:D,">=100",D:D,"<=500") adds everything in that range.
On the Formulas tab, in the Function Library. SUMIF sits under Math & Trig, while COUNTIF and AVERAGEIF are under More Functions, Statistical. Typing them directly is usually faster.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →