CODEWITHSUNDEEP
excelfunctionexcel-formulacounter
Vivisection
Vivisection

Reputation: 27

Countif function not acting how I would expect

So I have an excel function where I want to check to see if a string contains any of the strings mentioned, if it does, simply add 1 to the total, here's what I have:

=COUNTIF(C2:F2, "*offline")+COUNTIF(C2:F2, "*Expired")+COUNTIF(C2:F2, "*login")+COUNTIF(C2:F2, "*log in")

So for example if C2 says "login" D2 says "offline" , this function would only show "1".

Upvotes: 0

Views: 111

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Examples using just one Countif

If you want to count 1 then here is an example 

=IF(SUM(COUNTIF(C2:F2, {"*offline","*Expired","*login","*log in"}))>0,1,0)

If you want to count all instances then change the above formula to

=SUM(COUNTIF(C2:F2, {"*offline","*Expired","*login","*log in"}))

Screenshot

enter image description here

Upvotes: 1

Trenton Trama
Trenton Trama

Reputation: 4930

So, you don't really want a count, you just want to know if any of those cells have one of the values you're looking for?

Surround your function in an if statement and print 1 if it has a count at all. 

=IF(COUNTIF(C2:F2, "*offline")+COUNTIF(C2:F2, "*Expired")+COUNTIF(C2:F2, "*login")+COUNTIF(C2:F2, "*log in"),1,0)

Upvotes: 0

Related Questions