Function Problems

Count Text Longer Than 255 Characters in Excel with SUMPRODUCT

Aamir Naveed AkramAamir Naveed Akram Nov 20, 2026 869 views

Question details

Count matching identifiers when their text may be longer than 255 characters.

Count Text Longer Than 255 Characters in Excel with SUMPRODUCT
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.
Before you start

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.

Solution 1Recommended

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.

1
Select an output cell

Click an empty cell outside the identifier range, such as B1.

2
Enter the formula

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.

3
Check the result

Compare the count in B1 with the identifiers in A1:A5. The count includes A1 itself when it contains the lookup value.

Use SUMPRODUCT to count matching long text
Check for errors and blank cells: Errors in the compared cells can cause the formula to return an error. If the lookup cell is blank, the formula counts matching blank cells.
Count long identifiers

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. 1. Open the spreadsheet: Open WPS Spreadsheets and select File > Open to load your workbook.
  2. 2. Choose an output cell: Click an empty cell outside the identifier range, such as B1.
  3. 3. Enter SUMPRODUCT: Type =SUMPRODUCT((A1:A5=A1)+0), adjusting the range and lookup cell to match your sheet.
  4. 4. Review the count: Press Enter and check the result against the identifiers in your selected range.
Count identifiers longer than 255 characters with a formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.