What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Need a total, a count, a label, or a clean list from a spreadsheet? Choose an Excel function by the job: SUM and SUMIFS add values, COUNT and COUNTIFS count them, IF classifies a result, XLOOKUP retrieves a related value, and FILTER and UNIQUE return useful subsets. TEXTBEFORE extracts part of a text value, while LET makes longer formulas easier to follow.
Which Excel functions help analyze data?
These ten functions cover common tasks in tabular data: totaling, counting, classifying, retrieving, filtering, listing distinct values, and extracting text. They are a practical group, not a ranking of the ten best functions for every spreadsheet.
As an Amazon Associate I earn from qualifying purchases.
| Function | Task | Input shape | Criteria complexity |
|---|---|---|---|
| SUM | Add values | A range of numeric values | No criteria |
| SUMIFS | Add values that meet conditions | A sum range plus criteria ranges | Multiple criteria |
| COUNT | Count numeric entries | A range | No criteria |
| COUNTIFS | Count records that meet conditions | One or more criteria ranges | Multiple criteria |
| IF | Return one result or another | A logical test and two outcomes | One condition per IF |
| XLOOKUP | Retrieve a corresponding value | A lookup value, lookup range, and return range | One lookup condition |
| FILTER | Return matching rows | An array and an include condition | Condition-based filtering |
| UNIQUE | Return distinct values | An array or range | Deduplication |
| TEXTBEFORE | Extract text before a delimiter | A text value and delimiter | Delimiter-based |
| LET | Name intermediate calculations | Name/value pairs and a final calculation | Formula organization |
Examples below use a toy order table with region in column A, status in B, sales or other numeric amounts in C, and a lookup table whose IDs are in A and product names in B. Replace the sample ranges, columns, and criteria with those in your workbook.
How do I total values, including totals that meet criteria?
SUM: add a range
Use SUM for a straightforward total such as monthly sales or hours. It adds numeric values in the selected cells.
#1 Best Overall
=SUM(C2:C100)
SUMIFS: add only qualifying values
Use SUMIFS when a total must satisfy more than one condition, such as sales from one region with a completed status. Its arguments pair the range to add with ranges and conditions to check.
=SUMIFS(C2:C100,A2:A100,"West",B2:B100,"Complete")
For one condition, SUMIF is the corresponding single-criteria option. Microsoft notes that SUMIF can return incorrect results when a matching string exceeds 255 characters or is the string #VALUE!; this matters mainly when criteria involve unusually long text. See Microsoft’s SUMIF guidance.
How do I count numeric values or matching records?
COUNT: count numbers, not every filled cell
COUNT counts numeric entries. It is not a general nonblank-cell counter; use COUNTA when you want to count cells containing values more broadly.
=COUNT(C2:C100)
COUNTIFS: count records matching multiple conditions
Use COUNTIFS for questions such as how many orders are both from the West region and complete.
=COUNTIFS(A2:A100,"West",B2:B100,"Complete")
COUNTIF handles a single condition. Text criteria depend on consistent source data: extra spaces or inconsistent quotation marks can interfere with matching. Microsoft’s counting guidance describes COUNT and other ways to count worksheet values.
How do I classify a value with a condition?
IF: return one label when a test is true and another when it is false
Use IF for a simple decision rule, such as labeling a sale of at least 1,000 as High and other sales as Standard.
=IF(C2>=1000,"High","Standard")
The first argument is the test; the next two are the result for true and false. For more involved logic, keep the rule clear rather than nesting conditions without a plan.
Free tools Windows power users keep installed
One-click scans. No signup required.
How do I find a related value in another table?
XLOOKUP: return a value associated with an ID
Use XLOOKUP to search one range and return the corresponding value from another—for example, a product name for an item ID. The lookup and return ranges can be on either side of one another, and exact matching is the default.
=XLOOKUP(E2,A2:A100,B2:B100,"Not found")
Here, E2 contains the item ID, column A contains IDs, and column B contains product names. The fourth argument supplies a result for no match, avoiding an unhandled lookup error. Microsoft’s XLOOKUP documentation covers the function’s syntax and match options.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How do I filter rows or make a distinct list?
FILTER: return rows that satisfy a condition
Use FILTER when you want a live subset of a table, such as all orders from the West. The formula returns matching rows rather than just a count or total.
=FILTER(A2:C100,A2:A100="West","No rows")
The final argument supplies the result when the condition finds no rows.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →UNIQUE: list distinct values
Use UNIQUE to produce a list of distinct regions, customers, or categories without manually removing duplicates.
=UNIQUE(A2:A100)
For example, this returns distinct values from the region column. To sort the result as well, Excel has a separate SORT function; see Microsoft’s SORT documentation.
How do I extract text or make a formula easier to maintain?
TEXTBEFORE: take text before a delimiter
Use TEXTBEFORE when a code or label has a consistent separator and you need the portion before it. For a value such as ITEM-204, this returns ITEM.
=TEXTBEFORE(A2,"-")
LET: give an intermediate calculation a name
LET lets a formula name a calculation result and reuse it, making repeated expressions easier to read and maintain. This example calculates a net amount and then takes 10% of it.
=LET(net,C2-D2,net*0.1)
In this formula, net is the name assigned to C2-D2; the final expression uses that named value.
Which Excel versions support these formulas?
Do not assume every function in this list works in every Excel edition. Microsoft marks FILTER and UNIQUE as introduced in Excel 2021, and says XLOOKUP is unavailable in Excel 2016 and Excel 2019. Check the version used by anyone who needs to open or edit the workbook before sharing formulas. Microsoft’s function catalog lists functions by category and product availability.
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.
