Forum Discussion
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?
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?
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().
