Count Text Longer Than 255 Characters in Excel with SUMPRODUCT
Question details
Count matching identifiers when their text may be longer than 255 characters.

- Product
- Microsoft Excel and WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A spreadsheet contains identifiers that may exceed the text length supported by COUNTIF criteria.
- Observed behavior
- COUNTIF cannot reliably count matches for identifiers longer than 255 characters.
Identify the range containing your identifiers and the cell holding the value to count. The example below uses A1:A5 as the range and A1 as the lookup value.
Use SUMPRODUCT to count matching long text
Use this formula when COUNTIF cannot reliably compare a long identifier.
SUMPRODUCT compares each cell with the lookup value and adds the matching results. The comparison is not case-sensitive.
Click an empty cell outside the identifier range, such as B1.
Type =SUMPRODUCT((A1:A5=A1)+0) in B1 and press Enter. Replace A1:A5 and A1 with your own range and lookup cell if needed.
Compare the count in B1 with the identifiers in A1:A5. The count includes A1 itself when it contains the lookup value.

Count matching long text in WPS Spreadsheets
Use SUMPRODUCT in WPS Spreadsheets to compare full identifiers and count matches without relying on COUNTIF criteria.
- 1. Open the spreadsheet: Open WPS Spreadsheets and select File > Open to load your workbook.
- 2. Choose an output cell: Click an empty cell outside the identifier range, such as B1.
- 3. Enter SUMPRODUCT: Type =SUMPRODUCT((A1:A5=A1)+0), adjusting the range and lookup cell to match your sheet.
- 4. Review the count: Press Enter and check the result against the identifiers in your selected range.

Frequently Asked Questions
Why use SUMPRODUCT instead of COUNTIF for long text?
COUNTIF criteria can be unreliable for text longer than 255 characters. SUMPRODUCT can compare the cells directly, as in =SUMPRODUCT((A1:A5=A1)+0).
Does the formula count the lookup cell itself?
Yes. If the lookup cell is within the compared range, it is included in the count when it matches.
Is the comparison case-sensitive?
No. The equality comparison in =SUMPRODUCT((A1:A5=A1)+0) treats uppercase and lowercase letters as equal.
What happens if the lookup cell is blank?
The formula counts matching blank cells in the selected range. Check the lookup cell before interpreting the result.




