Countif with trim

[West Man]

I wish to do a countif trimming both the range and the criteria and don't know how to trim the range. =countif(A$2:A$10,TRIM(A2) but with a Trim of A2:A10. In other words a countif ignoring leading and trailing spaces.

 

[Jonmo1]

You will have better luck and fewer headaches if you just remove the trailing/leading spaces to begin with.

Here's a macro you can use to do that for you

Sub test()
Application.ScreenUpdating = False
For Each Cell In ActiveSheet.UsedRange
If Cell.HasFormula = False Then
    Cell.Value = Strings.Trim(Cell.Value)
End If
Next Cell
Application.ScreenUpdating = True
End Sub

 

[Aladin Akyurek]

I wish to do a countif trimming both the range and the criteria and don't know how to trim the range. =countif(A$2:A$10,TRIM(A2) but with a Trim of A2:A10. In other words a countif ignoring leading and trailing spaces.

=SUMPRODUCT((TRIM(A$2:A$10)=TRIM(E2))+0)

where E2 houses the thing to count.

Cheaper: Try to run ASAP Utilities (asap-utilities.com) on A2:A10 to remove excessive spaces.

 

[West Man]

Thank you Jonmo. I have an add-in from MacroSysyems that has an equivalent macro. This is for another user who does not have the add-in, so your code is helpful.

Aladin, spot on as always.

Thank you both.

 

[Apochockey]

=SUMPRODUCT((TRIM(A$2:A$10)=TRIM(E2))+0)

I have never really understood the SUMPRODUCT formula and need to look more into how it works, but just a quick question about the this one. Why the "+0" is needed for it function properly?