Share via

Using the COUNTIF Function to Identify Cells with More Than 255 Characters

Anonymous
2013-10-16T12:41:40+00:00

Is there a way to use the COUNTIF funtion in order to return a range of cells that exceeds a character count of more than 255?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-10-16T12:52:16+00:00

    Is there a way to use the COUNTIF funtion in order to return a range of cells that exceeds a character count of more than 255?

    Hi,

    You can do it with SUMPRODUCT

    =SUMPRODUCT(--(LEN(A1:A20)>255))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2013-10-16T13:17:39+00:00

    Hello,

    Thank you for your speedy reply! I clicked on a blank cell at the bottom of the spreadsheet and then copied the formula into that cell by pasting in the field next to the fx button at the top, adjusting the A1:A1000 to G1:G54, as those are the cells of interest. However, all I got was 0, when it is obvious that there are many cells with more than 255 characters. Did I paste it wrong? Or, is there a different way I am supposed to be using the formula?

    Thank you again!

    Katie

    Hi,

    If you copied and pasted the formula into a cell then provided the range was correct then it should have worked. Are you sure you didn't accidentally delete the -- in the formula because without those 2 minus signs it wont work. Here's the formula corrected for your range.

    =SUMPRODUCT(--(LEN(G1:G54)>255))

    Also try this formula in an empty cell (say) Z1. Drag down and it will tell you how many characters are in each cell.

    =LEN(G1)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-10-16T13:08:01+00:00

    Hello,

    Thank you for your speedy reply! I clicked on a blank cell at the bottom of the spreadsheet and then copied the formula into that cell by pasting in the field next to the fx button at the top, adjusting the A1:A1000 to G1:G54, as those are the cells of interest. However, all I got was 0, when it is obvious that there are many cells with more than 255 characters. Did I paste it wrong? Or, is there a different way I am supposed to be using the formula?

    Thank you again!

    Katie

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-10-16T13:07:46+00:00

    Hello,

    Thank you for your speedy reply! I clicked on a blank cell at the bottom of the spreadsheet and then copied the formula into that cell by pasting in the field next to the fx button at the top, adjusting the A1:A1000 to G1:G54, as those are the cells of interest. However, all I got was 0, when it is obvious that there are many cells with more than 255 characters. Did I paste it wrong? Or, is there a different way I am supposed to be using the formula?

    Thank you again!

    Katie

    Was this answer helpful?

    0 comments
  5. Anonymous
    2013-10-16T12:52:17+00:00

    To count how many cells exceed 255 charactrers:

    =SUMPRODUCT(--(LEN(A1:A1000)>255))

    Replace A1:A100 with the range of cells of interest....

    Was this answer helpful?

    0 comments