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

LittlegmoonBH

New Member
Joined
May 2, 2019
Messages
15
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?

 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,084
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you want to look for that exact text, use "=>Less than 5 years Old" as the criterion.
 

LittlegmoonBH

New Member
Joined
May 2, 2019
Messages
15
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,084
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Perhaps you could post the actual formula you are using?
 

LittlegmoonBH

New Member
Joined
May 2, 2019
Messages
15
It's just a simple countif

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,084
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
So does:

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

not work? If not, in what way?
 

LittlegmoonBH

New Member
Joined
May 2, 2019
Messages
15
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,084
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,187
Messages
5,473,023
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top