Countif with a Long Text String

[Groovicles]

Hi everyone,

I'm trying to run a countif formula in a separate worksheet searching for a long text string. The text string is long enough that I can't copy the entire string into the Ctrl F Find function.

=COUNTIF('Data Month 1'!C:C,F4)
F4 = text string put together using the values of multiple adjascent cells. Total characters are over 26.

Thanks!!

[XOR LX]

Hi,

So what's your question?

Regards

[Groovicles]

How can I adjust the countif formula to accept a very long text string? Sorry, I thought I made the issue evident. Thanks!

[XOR LX]

Is there an issue with having it in a cell and referenced from there?

Edit: wait - the limit is 255 characters. You have more than that?

Regards

[Jonmo1]

To my knowledge, the limit of a text string in an argument of a function is 255 characters.

Since you said the total characters are over 26, to me that indicates you are nowhere near that limit.
So you CAN do
=COUNTIF('Data Month 1'!C:C,"sometextstringupto255characterslong")

[Groovicles]

hi Jonmo,

thanks for the response. That's what I thought too. The text string is made up of of the value of a number of other cells. I've tried just using the value as well and it still didn't work. So worst case scenario lets say the text string is over 255 characters. Any way around this?

Thanks,

[Jonmo1]

Ok, Describe "Doesn't work"
Do you get an error? What error?
Do you get the wrong result? What result DO you get, and what result did you expect?

If the problem is the 255 character limit, you will get a clear error message saying that is the problem.
If you don't get that error, then that is not the problem

Post a sample workbook

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Groovicles]

I'm getting the #value error

[XOR LX]

I think we'd need to see an actual workbook to be able to see if we can come up with some workarounds to the 255 character limit which work for your particular set-up.

Regards

[Tony Valko]

Use SUMPRODUCT.

=SUMPRODUCT(--('Data Month 1'!C2:C100=F4))

Don't use the entire column as a reference. Use a smaller specific range.

[Groovicles]

Hi Tony,

I've tried that as well:

=SUMPRODUCT(--('Data Month 1'!$C$2:$C$6000=H4))

Still getting a #N/A error. Not sure what I'm missing here

[XOR LX]

Are you adamant that you can't provide a workbook then?

Regards

[Tony Valko]

Hi Tony,

I've tried that as well:

=SUMPRODUCT(--('Data Month 1'!$C$2:$C$6000=H4))

Still getting a #N/A error. Not sure what I'm missing here

Are there #N/A errors in the range?

Try this array formula**:

=SUM(IF(ISTEXT('Data Month 1'!$C$2:$C$6000),IF('Data Month 1'!$C$2:$C$6000=H4,1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.