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?

 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
If you want to look for that exact text, use "=>Less than 5 years Old" as the criterion.
 
Upvote 0
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
 
Upvote 0
So does:

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

not work? If not, in what way?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top