How to countif with a condition

Copper Contributor

I have a problem with a countif function.

 

I an array I have contents like (1/3), (2.5/5), (5/4.5) and (5/5).

=COUNTIF(E1:E1000;E1200) works very well when E1200 holds "(5/5)" and I want to know how many (5/5) there are in the array E1:E1000.

 

But I want to know how many (5/*) there are and how many (*/5) where * can be like 1 2 3 4 but also 1.5 2.5 3.5 and 4.5

I have a hunch that I should use the find function but cannot figure out how to use it in an array.

 

I'm happy if I can count everything that starts with (5/ or ends with /5), that will solve the problem for me.

 

Today I use a very clumsy solution with eight countifs, add them together, and subtract the total count with this sum. I'm sure there is a better solution. But what?


Can anyone help me?

4 Replies

@MonTor56 

ramizassaf_0-1596215382960.png

 

you can extract the data between ( 5 / 4.5 ) using these formulas, then you can use a normal countif formula!

 

does it solve the problem?

@MonTor56 

Did you try

=COUNTIF(E1:E1000; "*" & E1200)

if "/5)" or like is in E1200?

@Sergei Baklan 

 

No, I didn't. I tried it and I have to experiment with this idea a bit further. Nice trick!

The nice thing with this approach is that I don't need any helping cells to solve the problem.

Thank you Sergei! :)

@MonTor56 , you are welcome. You may use both "*" and "?" wildcards in COUNTIF().