Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Three Excel function groups can replace many repetitive spreadsheet steps: XLOOKUP retrieves a value that matches a key, SUMIFS and COUNTIFS total or count records that meet conditions, and FILTER returns matching rows or values. They solve different problems, so the useful shortcut is choosing the function that fits the task—not expecting one formula to replace every kind of spreadsheet work.
Which function should you use?
| Function | Use it to | Typical result |
|---|---|---|
| XLOOKUP | Find a key and retrieve related information | One corresponding value |
| SUMIFS | Add values that meet multiple conditions | One total |
| COUNTIFS | Count records or cells that meet multiple conditions | One count |
| FILTER | Return the rows or values that meet a condition | A potentially changing set of results |
The headline calls these “three functions,” grouping SUMIFS and COUNTIFS together because they handle the same broad job—conditional summaries—with different outputs. Microsoft’s documentation explains the mechanics of these functions but does not establish a typical amount of time users save.
As an Amazon Associate I earn from qualifying purchases.
How do you look up a value in Excel?
Use XLOOKUP to retrieve a related value
Suppose one column contains employee IDs and another contains employee names. XLOOKUP searches for an ID and returns the corresponding name. Microsoft Support describes it as searching a range or array and returning the item corresponding to the first match it finds.
Free tools Windows power users keep installed
One-click scans. No signup required.
The basic formula is:
=XLOOKUP(lookup_value, lookup_array, return_array)For example, if the ID to find is in E2, IDs are in A2:A100, and names are in B2:B100, use:
#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
=XLOOKUP(E2, A2:A100, B2:B100)The lookup array and return array are separate arguments. That means you do not need to count columns or provide a column index, and the return array can be to either side of the lookup array. Optional arguments let you specify what to display when there is no match, choose a match mode, and set the search direction. See Microsoft Support’s XLOOKUP function documentation for those options.
Check your Excel version before building a workbook around XLOOKUP: Microsoft says it is not available in Excel 2016 or Excel 2019. Those versions may open a workbook containing an XLOOKUP formula created in a newer version, but that does not mean they can create and calculate the function themselves.
How do you sum or count rows that meet multiple conditions?
Use SUMIFS for a conditional total
SUMIFS adds values only when the corresponding records meet every specified condition. For example, to total order amounts for one region and sales channel, put the values to add first, followed by matching criteria ranges and criteria:
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →=SUMIFS(D2:D100, A2:A100, "West", B2:B100, "Online")Here, D2:D100 contains order amounts, A2:A100 contains regions, and B2:B100 contains channels. All ranges cover the same rows, so Excel can test each order’s region and channel against its amount.
Use COUNTIFS for a conditional count
COUNTIFS counts records that satisfy multiple conditions rather than adding a numeric field. To count online orders from the West region whose amount is greater than 100:
=COUNTIFS(A2:A100, "West", B2:B100, "Online", D2:D100, ">100")Each criteria range should align with the others, and each criterion applies to its corresponding range. Microsoft’s Excel functions by category index describes SUMIFS as adding cells that meet multiple criteria and COUNTIFS as counting cells that meet multiple criteria.
Rank #4
How do you filter data with a formula?
Use FILTER to return matching records
FILTER returns the data selected by a TRUE/FALSE include array. Its syntax is:
Recommended Free Tools
=FILTER(array, include, [if_empty])To return rows A2:D100 where the region in column A is West:
=FILTER(A2:D100, A2:A100="West", "No matches")The optional third argument supplies a result when no rows match. Without it, an empty result produces #CALC!. Microsoft Support explains the function and its arguments in the FILTER function documentation.
To require both West region and Online channel, multiply the conditions; to allow either condition, add them:
=FILTER(A2:D100, (A2:A100="West")*(B2:B100="Online"), "No matches")=FILTER(A2:D100, (A2:A100="West")+(B2:B100="Online"), "No matches")FILTER can return multiple rows and spill them into neighboring cells. Keep the cells where the results need to appear clear; occupied cells in the spill area can prevent the full result from displaying. Microsoft describes this behavior in its guide to dynamic array formulas and spilled array behavior.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
There is also a cross-workbook limitation: linked dynamic arrays have limited support. For this scenario, the source and destination workbooks need to remain open; refreshing a linked formula after closing the source workbook can result in #REF!.
Quick Recap
Choose by the result you need
- Need one related value for a key? Use XLOOKUP, if your Excel version supports it.
- Need a total based on several conditions? Use SUMIFS.
- Need to count matching records? Use COUNTIFS.
- Need the matching records themselves? Use FILTER, include an empty-result value when appropriate, and leave its spill area available.
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.
