iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
When a spreadsheet has hundreds of rows, the useful question is usually specific: What is the total for one region? How many orders are complete? Which price belongs to this item code? Excel’s SUM, SUMIFS, COUNTIFS, XLOOKUP, and other functions turn those questions into repeatable formulas. The ten functions below cover common tasks for summarizing, classifying, retrieving, filtering, and cleaning tabular data; they are a practical selection, not a ranking of the best functions for every workbook.
Choose a function by the job
For a simple total, use SUM; for a total restricted by conditions, use SUMIFS. Use COUNT to count numeric entries and COUNTIFS to count records matching multiple conditions. IF classifies a value, XLOOKUP retrieves a corresponding value, FILTER returns matching rows, UNIQUE returns distinct values, TEXTBEFORE extracts part of a text string, and LET names intermediate calculations.
| Function | Primary task | Input shape | Criteria complexity |
|---|---|---|---|
SUM |
Add numeric values | A range of numbers | No criteria |
IF |
Return one result or another | A condition and two outcomes | One logical test |
SUMIFS |
Add values that meet conditions | Sum range plus matching criteria ranges | Multiple criteria |
XLOOKUP |
Retrieve a corresponding value | Lookup value, lookup range, return range | Match a lookup value |
COUNTIFS |
Count records that meet conditions | One or more criteria ranges | Multiple criteria |
COUNT |
Count numeric entries | A range | No criteria |
LET |
Name intermediate calculation results | Name/value pairs and a final calculation | Depends on the calculation |
FILTER |
Return matching rows | An array or range and an include condition | One or more include conditions |
UNIQUE |
Return distinct values | An array or range | Distinctness, not a criteria test |
TEXTBEFORE |
Extract text before a delimiter | Text and delimiter | Delimiter-based |
Examples use a simple orders table: column A contains region, B status, C sales, and D a product name. Replace the sample ranges and criteria with the columns and values in your own workbook. These formulas are illustrative; they are not tested against a particular file.
Which Excel functions help analyze data?
1. SUM: add values
Use SUM for a straightforward total, such as revenue or hours. For sales in C2:C100:
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
=SUM(C2:C100)
2. IF: apply a simple rule
Use IF to label a record based on a condition. This example marks sales of at least 1,000 as High:
=IF(C2>=1000,"High","Standard")
3. SUMIFS: total values that meet multiple criteria
Use SUMIFS when a total must satisfy more than one condition—for example, sales for the West region with a Complete status:
=SUMIFS(C2:C100,A2:A100,"West",B2:B100,"Complete")
The first range is the values to add; the following range-and-criterion pairs restrict which rows count. For one criterion, the related function is SUMIF.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minute4. XLOOKUP: retrieve a matching value
Use XLOOKUP to find a value in one range and return the corresponding item from another. If E2 contains an item ID, A2:A100 contains IDs, and B2:B100 contains product names:
=XLOOKUP(E2,A2:A100,B2:B100,"Not found")
XLOOKUP uses exact matching by default and can return the supplied not-found message when no match exists. Unlike older lookup patterns that depend on the return column being to the right, its lookup and return ranges can be on either side of one another. Microsoft documents its syntax and behavior in the XLOOKUP function reference.
5. COUNTIFS: count records that meet multiple criteria
Use COUNTIFS to answer questions such as how many orders are both from the West and Complete:
=COUNTIFS(A2:A100,"West",B2:B100,"Complete")
For a single condition, use COUNTIF. Keep text values consistent: extra spaces or inconsistent quotation marks can interfere with matching. Microsoft describes counting options in Ways to count values in a worksheet.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
6. COUNT: count numeric entries
COUNT counts cells containing numbers; it is not a general count of nonblank cells. For numeric entries in the sales column:
=COUNT(C2:C100)
If you need to count text and other populated cells as well, COUNTA is the relevant alternative. Microsoft distinguishes the functions in its Excel functions by category reference.
Rank #4
7. LET: give an intermediate result a name
Use LET to make a formula easier to read when it reuses a calculation. This example names the difference between sales and costs, then calculates 10% of it:
=LET(net,C2-D2,net*0.1)
The name net is local to the formula; it does not create a worksheet cell or named range.
Recommended Free Tools
8. FILTER: return rows matching a condition
Use FILTER to create a live subset of a table. This returns rows from A2:C100 where the region is West and displays a message if there are no matching rows:
=FILTER(A2:C100,A2:A100="West","No rows")
The returned results can spill into neighboring cells, so leave space for them. This is useful for a changing view of the source data rather than a manually copied list.
9. UNIQUE: create a distinct-value list
Use UNIQUE to produce a list of regions without manually removing duplicates:
=UNIQUE(A2:A100)
As with other dynamic-array results, make sure the cells where the list will spill are available.
10. TEXTBEFORE: extract text before a separator
Use TEXTBEFORE when a code or label has a consistent delimiter. To return the portion of A2 before its first hyphen:
=TEXTBEFORE(A2,"-")
Which versions support these formulas?
Compatibility matters when a workbook will be opened by people using different Excel editions. Microsoft’s function catalog marks FILTER and UNIQUE as introduced in Excel 2021. Microsoft’s dedicated XLOOKUP reference says the function is unavailable in Excel 2016 and Excel 2019. Check the recipient’s version before sharing a formula that depends on one of these newer functions; the catalog lists supported product editions on its Excel functions by category page.
Quick Recap
What to check when a formula gives an unexpected result
- Confirm that each criteria range covers the same rows as the range being summed or counted.
- Check text criteria for extra spaces and inconsistent spelling or punctuation; matching is only as reliable as the source values.
- For
COUNT, verify that the cells contain numbers rather than text that looks like a number. UseCOUNTAwhen the goal is to count populated cells generally. - For
FILTERandUNIQUE, ensure cells where results need to spill are clear and that the installed Excel version supports the function. - For
XLOOKUP, confirm that the lookup value exists in the lookup range or use its optional not-found argument to handle missing matches clearly.
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.
