Recommended Free Tools
Excel’s conditional sum, count, and average functions can handle recurring calculations across rows; XLOOKUP can fetch a related value, and IFERROR can provide a deliberate fallback when a formula fails. Here are five formulas you can adapt to your own columns—without manually repeating the same calculation.
Set up a simple example table
Assume your worksheet has headers in row 1 and data in rows 2 through 100, arranged like this:
As an Amazon Associate I earn from qualifying purchases.
| Date | Region | Product | Units | Sales |
|---|---|---|---|---|
| A | B | C | D | E |
In the formulas below, replace these sample ranges and criteria with the columns and values in your workbook. The formulas are examples of how to write the calculations; they are not reported results from a tested workbook.
1. SUMIF: add amounts that meet a condition
Use SUMIF when you repeatedly need a total for rows matching one condition—for example, sales in the East region. It checks the region column and adds the corresponding sales values:
=SUMIF(B2:B100,"East",E2:E100)
The first range contains the cells Excel checks, the text "East" is the criterion, and the last range contains the numbers to add. For multiple conditions, use SUMIFS; Microsoft describes it as adding cells that meet multiple criteria (Microsoft’s Excel functions by category).
2. COUNTIF: count matching entries
Use COUNTIF to count cells that meet one criterion, such as the number of rows whose product is Widget:
=COUNTIF(C2:C100,"Widget")
The criterion can be text, a number, an expression, or a cell reference. For example, to count values greater than 10, use =COUNTIF(D2:D100,">10"). If the condition should combine multiple criteria, use COUNTIFS instead. Microsoft’s COUNTIF documentation explains the one-criterion function and provides examples.
3. AVERAGEIF: average values for matching rows
Use AVERAGEIF when you need the average of values associated with rows matching one condition. To average sales for the East region:
Rank #3
=AVERAGEIF(B2:B100,"East",E2:E100)
The criteria range is column B, while the average range is column E. This distinction matters when Excel should test one set of cells but average another. Its syntax is AVERAGEIF(range, criteria, [average_range]); if you omit average_range, Excel averages the criteria range instead. See Microsoft’s AVERAGEIF documentation for the function’s syntax and examples.
4. XLOOKUP: return a corresponding value
Use XLOOKUP to find a value in one range and return the corresponding item from another. For example, if a lookup value is in G2, column A contains the values to search, and column E contains the sales values to return:
=XLOOKUP(G2,A2:A100,E2:E100,"Not found")
Check that the lookup and return ranges match the actual layout of your workbook. The fourth argument supplies the result to display when there is no match. Microsoft lists XLOOKUP as a function for finding a value in a range or array and returning a corresponding item (Excel functions by category).
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
5. IFERROR: show a useful fallback for an error
Use IFERROR when you want a formula to display a chosen fallback if the expression evaluates to an error. For example:
=IFERROR(XLOOKUP(G2,A2:A100,E2:E100),"Check input")
This shows Check input if the lookup expression errors. Use a message that helps someone decide what to do next, and investigate errors when they may indicate a bad range, incorrect input, or another problem you need to fix. A fallback should not conceal an error that matters. Microsoft includes IFERROR in its function category reference.
Choose the formula by the task
| Repeated task | Function | Condition |
|---|---|---|
| Add matching values | SUMIF | One criterion; use SUMIFS for multiple criteria |
| Count matching cells | COUNTIF | One criterion; use COUNTIFS for multiple criteria |
| Average values for matching rows | AVERAGEIF | One criterion |
| Find a value and return a related item | XLOOKUP | Match a lookup value to its corresponding result |
| Display a fallback when a formula errors | IFERROR | Formula evaluates to an error |
These functions address different jobs: summing, counting, averaging, retrieving a related value, and handling an error. Choose based on what you want Excel to do, then replace the example ranges and criteria with those from your sheet.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
