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

[LittlegmoonBH]

Hello!

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?

 

[RoryA]

If you want to look for that exact text, use "=>Less than 5 years Old" as the criterion.

 

[LittlegmoonBH]

Hi,

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.

Thanks

 

[RoryA]

Perhaps you could post the actual formula you are using?

 

[LittlegmoonBH]

It's just a simple countif

=COUNTIF(Open!E:E,Sheet2!E13)

 

[RoryA]

So does:

=COUNTIF(Open!E:E,"="&Sheet2!E13)

not work? If not, in what way?

 

[LittlegmoonBH]

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.

 

[RoryA]

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.

 

[LittlegmoonBH]

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

Thanks for your help

 

[RoryA]

Glad to help.