Challenge with countif limitations

[PatrickK777777]

Hello guys. I have a problem here. Am doing some complex data manipulation requiring use of the countif function repeatedly, but I just realized this function breaks down for criteria values longer than 255 characters. I've tried using a custom modifiedcountif function to bypass this limitation but it is really slow. Any better suggestions on this?!

 

[Joe4]

Can you show us exactly what it is that you are trying to do, and what your formula looks like?

Also, I don't know if it would help here, but note that there is a COUNTIFS function, that allows for multiple criteria.

 

[PatrickK777777]

Can you show us exactly what it is that you are trying to do, and what your formula looks like?

Also, I don't know if it would help here, but note that there is a COUNTIFS function, that allows for multiple criteria.

I've noticed Countifs breaks down the same way when the criteria has a length of over 255. ModifiedCountif is like

Function ModifiedCountif(ReferenceRange as range, Criteria as variant) as long
Dim Cell as Range, ModifiedReferenceRange as Range

If len(Criteria)<=255 then
ModifiedCountif = Application.worksheetfunction.countif(ReferenceRange, Criteria)
Exit Function

Else
Set ModifiedReferenceRange = UsedRangeWithinRange(ReferenceRange)
ModifiedCountif = 0

For each Cell in ModifiedReferenceRange
If cell.value = criteria then
ModifiedCountif = ModifiedCountif + 1
End if
Next Cell
End if

End function

 

[PatrickK777777]

UsedRangeWithinRange is a custom function considering only the used range in the provided range

I've noticed Countifs breaks down the same way when the criteria has a length of over 255. ModifiedCountif is like

Function ModifiedCountif(ReferenceRange as range, Criteria as variant) as long
Dim Cell as Range, ModifiedReferenceRange as Range

If len(Criteria)<=255 then
ModifiedCountif = Application.worksheetfunction.countif(ReferenceRange, Criteria)
Exit Function

Else
Set ModifiedReferenceRange = UsedRangeWithinRange(ReferenceRange)
ModifiedCountif = 0

For each Cell in ModifiedReferenceRange
If cell.value = criteria then
ModifiedCountif = ModifiedCountif + 1
End if
Next Cell
End if

End function

 

[Joe4]

Why are you using VBA?

You have shown us some custom UDFs, but not any examples of how you are trying to use it.
If we could understand/see exactly what it is you are trying to do, we may be able to offer other alternatives.

 

[PatrickK777777]

Am using vba because of the problem illustrated in the UDF above. If criteria length is greater than 255, normal countif breaks down.

What am trying to do is:
Am having a list of items being sold on sheet1, and things being sold are entered on sheet2, but on data entry, each customer gets all his/her items entered into a single cell with the same receipt number.

I've built an algorithm to split these items automatically so as each item's sales are followed up at an individual level. This algorithm uses the countif function as an intermediate.

The problem is that Occasionally this string can build to a length above 255, which creates a problem for the steps utilising the countif/countifs functions.

Why are you using VBA?

You have shown us some custom UDFs, but not any examples of how you are trying to use it.
If we could understand/see exactly what it is you are trying to do, we may be able to offer other alternative.

 

[Joe4]

Take a look here at the part highlighted in purple (which address your issues): COUNTIF function - Microsoft Support).

 

[PatrickK777777]

Take a look here at the part highlighted in purple (which address your issues): COUNTIF function - Microsoft Support).

thanks. Tseems this means splitting the string into two and recombining it while using the countif function, right?!

 

[PatrickK777777]

thanks. Tseems this means splitting the string into two and recombining it while using the countif function, right?!

Tried using the & operator with this interpretation but same problem

 

[Joe4]

Tried using the & operator with this interpretation but same problem

Did you try that method in VBA, or in a straight COUNTIF function right on your worksheet?
If you did not try the later, try it now and see if that makes a difference.