SUMIFS, COUNTIFS, and XLOOKUP: Practical Excel Formulas
SUMIFS, COUNTIFS, and XLOOKUP answer three different business questions: how much, how many, and which value belongs to this record? Choose the question first, then use the formula and a separate control to check the answer.
Choose the right function
| Question | Function | Important limit |
|---|---|---|
| How much revenue meets these conditions? | SUMIFS | Conditions in one formula are combined with AND |
| How many rows meet these conditions? | COUNTIFS | It counts rows, not distinct customers |
| Which customer owns this invoice? | XLOOKUP | The default search returns the first matching record |
The examples use English function names and comma separators. XLOOKUP is available in supported newer Excel versions, including Microsoft 365 and Excel 2021/2024, but not Excel 2016 or 2019. Check compatibility before sharing a workbook. Regional separators can differ.
Set up a reproducible source table
Use the following original four-invoice dataset. Convert it to an Excel table named Sales. Keep dates as real dates, amounts as numbers, and InvoiceID as text. One row represents one invoice, so each nonblank invoice identifier should be unique within this fictional company.
| Date | InvoiceID | Service | Customer | Revenue | DirectCost |
|---|---|---|---|---|---|
| 2026-01-05 | 001 | Bookkeeping | Alpha | 1,000 | 600 |
| 2026-01-12 | 002 | Advisory | Beta | 1,500 | 600 |
| 2026-02-03 | 003 | Bookkeeping | Alpha | 500 | 400 |
| 2026-02-15 | 004 | Advisory | Gamma | 2,000 | 800 |
The control totals are four invoices, revenue 5,000, and direct cost 2,400. These are the same source rows used in our PivotTable lesson. You can compare the formula results with the summary instead of trusting either one alone.
SUMIFS: total matching amounts
=SUMIFS(Sales[Revenue],Sales[Service],"Bookkeeping")Expected answer: 1,500, from 1,000 + 500. SUMIFS starts with the amount range, followed by pairs of criteria ranges and criteria. Keep every range aligned to the same records.
For Bookkeeping revenue in February 2026, use a lower date boundary and an exclusive upper boundary:
=SUMIFS(Sales[Revenue],Sales[Service],"Bookkeeping",
Sales[Date],">="&DATE(2026,2,1),
Sales[Date],"<"&DATE(2026,3,1))Expected answer: 500. The conditions mean Bookkeeping AND on or after February 1 AND before March 1. This boundary pattern also includes times throughout the final day when date-time values are present. An amount of zero can mean no matching rows, or matching amounts that net to zero; investigate which applies.
COUNTIFS: count qualifying rows
=COUNTIFS(Sales[Service],"Advisory",Sales[Revenue],">1000")Expected answer: 2, for invoices 002 and 004. Both conditions must hold on the same row. Alpha has two invoice rows, but only one distinct customer identity. Do not label a row count as a unique-customer count.
When a criterion lives in a cell, keep operators in quotes and join the reference. If H2 contains 1000, ">"&H2 means greater than the value in H2. Putting ">H2" inside quotes makes H2 part of the criterion text instead of a reference.
XLOOKUP: return the related value
Enter the text identifier 003 in H2. Then:
=XLOOKUP(H2,Sales[InvoiceID],Sales[Customer],"Not found",0)Expected answer: Alpha. The explicit 0 requests an exact match. The lookup and return arrays refer to corresponding rows. Try 999: the result should be Not found, not zero or a guessed nearest invoice.
A missing match is different from a matching record whose return field is blank. Check the underlying customer field if an apparently valid ID produces an unexpected blank or zero. Also check leading zeros, stray spaces, and whether one side stores an ID as a number while the other stores text.
Check uniqueness before trusting a lookup
=COUNTIFS(Sales[InvoiceID],H2)For 003, the expected count is 1. A count of 0 means no matching invoice; greater than 1 means the key is not unique. If you add another row with ID 003 and a different customer, XLOOKUP’s default first-to-last search returns the first match. It does not warn that the source is ambiguous.
For a multi-company dataset, define a key that includes the company. At invoice-line grain, a repeated invoice ID can be legitimate, so an invoice-level lookup may require a separate header table. Fix the record definition before deleting apparent duplicates.
A short checking routine
- Write the business question and reporting scope beside the formula.
- Manually identify the matching source rows in this small example.
- Compare the amount or count with the expected answer.
- Test no match, one match, duplicate keys, and a date at the month boundary.
- After adding data, confirm the table includes it and repeat the checks.
SUMIFS and COUNTIFS support wildcards in text criteria. If IDs contain literal asterisks or question marks, define how those characters should be matched and escaped rather than assuming a criteria function treats them as plain text. Keep error messages visible until their cause is understood.
Frequently asked questions
Should I use XLOOKUP to total invoices?
Use SUMIFS when the intended result aggregates all qualifying amounts. XLOOKUP normally returns a related result from a matching position, rather than summing all matches.
Why does COUNTIFS disagree with the number of customers?
It counts qualifying rows. A customer can appear in several rows, so that is not automatically a distinct-customer count.
Does Not found handle every data problem?
No. It handles an absent match, not duplicate keys, incorrect source values, blank customer fields, or mismatched business definitions.
Can I use the formulas in Excel 2019?
SUMIFS and COUNTIFS are supported there, but XLOOKUP is not. Use an appropriate supported alternative and test the shared workbook.
Sources and further learning
Official documentation reviewed November 3, 2026. Worked examples, learning sequences, and practice answers are original teaching illustrations. Product interfaces and availability can vary by version, platform, plan, and region.