Countif formula returning the same results for entire data range that start with a prefix of < >


New Member

I'm building a dashboard and using
“COUNTIF” to simply count the number of times a complaint isowned by a particular person or process… The issue I’m having is new ownershave been added to the data and the begin with < or > for example

<over font="" old<="" 5years=""></over>

>Less than 5 years Old

These have been used to implement a temporary process andthere’s over 20 of them. My COUNTIF is looking at all of the options in exactlythe same way and giving me the same volume.

How do I tell my formula to ignore the < or > prefix?



New Member

Thanks for your response, but that’s not what I’m trying todo, it’s difficult to explain in brief, see below a better example.

The issue is, I’m trying to count the number of times that acase has the “Owner Name”

“<5 Years Old – 1 – 10 cases”
“<5 Years Old – 11 – 20 cases”
“<5 Years Old – 21 – 30 cases”
“<5 Years Old – 31 – 40 cases”

And so on…

This also repeats;

“>5 Years Old – 1 – 10 cases”
“>5 Years Old – 11 – 20 cases”
“>5 Years Old – 21 – 30 cases”
“>5 Years Old – 31 – 40 cases”

And so on…

The issue is the COUNTIF returns the same count for all 20 “OwnerNames” which I know is incorrect. I need the “COUNTIF” to see past the <> and count the text beyond this to ensure accuracy.



New Member
It works, but it's returning the same result / count across all of the different "Owner Name", the returned count appears to be counting < and ignoring the remaining text in the field.

All "Owner Names" beginning < have a count of 6290 (which I know is not true)
All "Owner Names" beginning > have a count o 2109 ( which I know is not true)

However, there are 6290 "Owner Names" beginning with < but they need to split out or counted in line with the full "Owner Name" as above. The formula appear to be counting < ignoring all text that follows the <

I've tried to change the cell format with no joy.


MrExcel MVP, Moderator
What exactly is an "owner name"? The formula I posted will not ignore text following a < or > sign - the = sign will treat those as literal text rather than as comparison operators.


New Member
Apologies, I didn't read your formula properly. It does work!

Thanks for your help

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...