Formula Errors

Excel Formula to Return Yes for First Text String Occurrence in Same Month

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs an Excel formula to check if a specific text string (such as an address) has already appeared in a previous row within the exact same month and year. It should return 'Yes' for the first appearance and 'No' for subsequent duplicates.

Product: Excel
Device & OS: not provided
Scenario: Filtering or identifying the first unique occurrences of specific data entries within specific monthly periods.
Observed behavior: The user requires a logical formula structure that outputs 'Yes' for the initial match in a given month and 'No' for any later occurrences of the same string within that same time frame.

Before you start

Ensure your dataset contains a dedicated column with correctly formatted dates (not text) and another column containing the text strings you wish to evaluate.

Solution 1Recommended

Use SUMPRODUCT and TEXT Functions to Identify First Occurrences

Apply a combination of IF, SUMPRODUCT, and TEXT functions with an expanding range to accurately flag the first occurrence of a text string in the specified month.

This approach uses the TEXT function to extract the month and year from your date column, ensuring the comparison is restricted to the current month. The SUMPRODUCT function then evaluates the data from the top of the list down to the current row. By using an expanding range reference, it counts how many times the criteria have been met so far. If the count equals 1, it is the first occurrence.

Select the Target Cell

Click on cell E2 (or the first cell in your designated output column) where you want the 'Yes' or 'No' result to be displayed.

Enter the Formula

Type or paste the following formula into the formula bar: =IF(SUMPRODUCT((D$2:D2=D2)*(TEXT(A$2:A2,"mmyy")=TEXT(A2,"mmyy")))=1,"Yes","No")

Verify Cell References

Ensure that column A corresponds to your dates and column D corresponds to your text strings. The absolute references (like D$2 and A$2) anchor the beginning of your range, while the relative references (like D2 and A2) expand as the formula is copied downward.

Apply to the Entire Column

Press Enter to finalize the formula, then click and drag the fill handle at the bottom-right corner of cell E2 down to apply it to all the remaining rows in your dataset.

Expanding Range Logic: By locking only the starting row with a dollar sign ($), the formula creates an expanding range that evaluates data dynamically up to the current row without calculating future rows.

Efficient Data Processing

Easily Handle Complex Array Formulas with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced logical evaluations and array formulas like SUMPRODUCT. You can seamlessly apply complex matching criteria to your data without experiencing lag or formatting errors.

1. Open Your Data File: Launch WPS Spreadsheet and open the document containing your data logs.
2. Insert the Formula: Select the cell where you want the result, paste the SUMPRODUCT formula, and rely on WPS's color-coded brackets to verify your references.
3. Auto-fill Down: Double-click the fill handle in the bottom right corner of the active cell to instantly apply the formula to all rows in your table.

Full compatibility with Microsoft Excel formulas and functionsBuilt-in syntax highlighting and error-checking for complex equationsFast processing speeds when applying expanding ranges to large datasetsFree, lightweight, and features a user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMPRODUCT formula return an error value like #VALUE!?

This commonly occurs if the ranges in your formula are not the exact same size. Ensure that both ranges (e.g., A$2:A2 and D$2:D2) cover the exact same number of rows. Also, verify that your date column contains valid date values rather than text.

Can I customize the formula to return something other than 'Yes' and 'No'?

Yes. You can change the output by modifying the final two arguments of the IF function. For example, replace "Yes" and "No" with "First" and "Duplicate" to suit your reporting needs.

How do I modify this formula to check for the first occurrence in the same year, regardless of the month?

To check for occurrences within the same year only, change the formatting argument in the TEXT function from "mmyy" to "yyyy". The formula will then compare only the years.

Excel & Data Problems