How to countif with a condition

%3CLINGO-SUB%20id%3D%22lingo-sub-1558814%22%20slang%3D%22en-US%22%3EHow%20to%20countif%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558814%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20problem%20with%20a%20countif%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20an%20array%20I%20have%20contents%20like%20(1%2F3)%2C%20(2.5%2F5)%2C%20(5%2F4.5)%20and%20(5%2F5).%3C%2FP%3E%3CP%3E%3DCOUNTIF(E1%3AE1000%3BE1200)%20works%20very%20well%20when%20E1200%20holds%20%22(5%2F5)%22%20and%20I%20want%20to%20know%20how%20many%20(5%2F5)%20there%20are%20in%20the%20array%20E1%3AE1000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20want%20to%20know%20how%20many%20(5%2F*)%20there%20are%20and%20how%20many%20(*%2F5)%20where%20*%20can%20be%20like%201%202%203%204%20but%20also%201.5%202.5%203.5%20and%204.5%3C%2FP%3E%3CP%3EI%20have%20a%20hunch%20that%20I%20should%20use%20the%20find%20function%20but%20cannot%20figure%20out%20how%20to%20use%20it%20in%20an%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20happy%20if%20I%20can%20count%20everything%20that%20starts%20with%20(5%2F%20or%20ends%20with%20%2F5)%2C%20that%20will%20solve%20the%20problem%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EToday%20I%20use%20a%20very%20clumsy%20solution%20with%20eight%20countifs%2C%20add%20them%20together%2C%20and%20subtract%20the%20total%20count%20with%20this%20sum.%20I'm%20sure%20there%20is%20a%20better%20solution.%20But%20what%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ECan%20anyone%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1558814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558987%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20countif%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746074%22%20target%3D%22_blank%22%3E%40MonTor56%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ramizassaf_0-1596215382960.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209367i23EAD0B96894875B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22ramizassaf_0-1596215382960.png%22%20alt%3D%22ramizassaf_0-1596215382960.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20can%20extract%20the%20data%20between%20(%205%20%2F%204.5%20)%20using%20these%20formulas%2C%20then%20you%20can%20use%20a%20normal%20countif%20formula!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edoes%20it%20solve%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559542%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20countif%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559542%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746074%22%20target%3D%22_blank%22%3E%40MonTor56%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20try%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(E1%3AE1000%3B%20%22*%22%20%26amp%3B%20E1200)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20%22%2F5)%22%20or%20like%20is%20in%20E1200%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560018%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20countif%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20I%20didn't.%20I%20tried%20it%20and%20I%20have%20to%20experiment%20with%20this%20idea%20a%20bit%20further.%20Nice%20trick!%3C%2FP%3E%3CP%3EThe%20nice%20thing%20with%20this%20approach%20is%20that%20I%20don't%20need%20any%20helping%20cells%20to%20solve%20the%20problem.%3C%2FP%3E%3CP%3EThank%20you%20Sergei!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561675%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20countif%20with%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746074%22%20target%3D%22_blank%22%3E%40MonTor56%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20You%20may%20use%20both%20%22*%22%20and%20%22%3F%22%20wildcards%20in%20COUNTIF().%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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().