Excel COUNTIFS Formula to Match a Name and Date in the Same Row
Question details
Create a formula that confirms whether a name and a date appear together in one row of an Excel table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to return YES when the name in A109 and the date in D105 are recorded in the same row of Table1.
- Observed behavior
- Separate MATCH formulas can find the name and date in different rows, producing an incorrect YES result.
Confirm that Table1 contains the columns named Name adjusted and Leave Date, and that the date in D105 is a real date value rather than text. Check that both table columns cover the same set of rows.
Use COUNTIFS to test the name and date in the same table row
Use this formula when both criteria must be true for one record, rather than being found independently anywhere in the table.
COUNTIFS counts only rows that meet every listed condition. If the count is greater than zero, the specified name and leave date exist together in at least one row of Table1.
Click the cell where you want Excel to display YES or NO.
Type or paste =IF(COUNTIFS(Table1[Name adjusted],A109,Table1[Leave Date],$D$105)>0,"YES","NO") and press Enter.
Excel returns YES when a single row in Table1 contains both the name from A109 and the date from D105; otherwise, it returns NO.
Check Same-Row Name and Date Matches with WPS Spreadsheets
WPS Spreadsheets lets you use COUNTIFS to test multiple conditions in one table row. Enter the formula in a result cell to identify whether a name and date belong to the same record.
- 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls workbook, then select the worksheet containing Table1.
- 2. Choose the output cell: Click the cell where the match result should appear.
- 3. Enter the COUNTIFS formula: Enter =IF(COUNTIFS(Table1[Name adjusted],A109,Table1[Leave Date],$D$105)>0,"YES","NO").
- 4. Confirm the formula: Press Enter to return YES when both values occur in the same Table1 row, or NO when no matching row exists.
Frequently Asked Questions
Why do separate MATCH formulas return YES when the name and date are on different rows?
Each MATCH formula searches its own range independently. One can find the name in one row while the other finds the date in another row, so they do not prove that the two values belong to the same record.
Can I use COUNTIFS without an Excel table?
Yes. Replace structured references with matching cell ranges, such as =IF(COUNTIFS(B2:B100,A109,C2:C100,$D$105)>0,"YES","NO"). Both ranges must start and end on the same rows.
What should I check if COUNTIFS returns NO even though the row looks correct?
Check that the name text has no extra spaces and that the Leave Date values are actual dates. You can compare date formatting or use TRIM on name values if hidden spaces may exist.
Does this formula work if the name and date appear more than once?
Yes. COUNTIFS returns the number of rows meeting both conditions, and the formula returns YES whenever that number is greater than zero.
