New Contributor

# How to countif with a condition

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

# Re: How to countif with a condition

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?

# Re: How to countif with a condition

Did you try

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

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

# Re: How to countif with a condition

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!

# Re: How to countif with a condition

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