CODEWITHSUNDEEP
excelexcel-formulacountif
G. Dimitriou
G. Dimitriou

Reputation: 67

How to create a non-specific COUNTIF formula?

I'm looking for a way to be able to make my COUNTIF formula non-specific, e.g. right now I'm placing a 1 next to values that are equal, however in some instances were a value such as 3004785124 is compared against a value which contains the number and should equal a cound (3004785124 // 52-123456) it doesn't work as it is not specifically the value.

Does anyone know a way in which I could fix this problem?

How I want my spreadsheet to look:

        A1          A2                      A3
    -----------------------------------------------
    3004785124  3004785124 // 52-237609     1

How my spreadsheets currently looking:

    A1          A2                            A3
------------------------------------------------------------  
3004785124  3004785124 // 52-237609     0 (=COUNTIF(A1, A2)

Upvotes: 2

Views: 84

Answers (1)

CallumDA
CallumDA

Reputation: 12113

Thanks for the example, that made your question a lot clearer.

You can check if one string can be found in another using the SEARCH function

=IF(ISERROR(SEARCH(A1,A2)),0,1)

If A1 can't be found in A2 then the SEARCH function returns #VALUE error, so we just need to deal with that error by wrapping a ISERROR function around it.

I'll add a little more explanation as per your request.

The search function works as follows and either returns an integer if your string is found or a #VALUE error if your string isn't found

=SEARCH(find_what, in_what)
=SEARCH("a","abc") 'returns 1
=SEARCH("c","abc") 'returns 3
=SEARCH("d","abc") 'returns #VALUE

Carrying on with those examples, ISERROR(some_value) will either return TRUE if some_value is any error, or FALSE otherwise

=ISERROR(SEARCH("c","abc")) 'returns FALSE
=ISERROR(SEARCH("d","abc")) 'returns TRUE

Now it's quite simple,

=IF(some_condition, value_if_true, value_if_false)

some condition must either be TRUE or FALSE so that's where we put the previous expressions

=IF(ISERROR(SEARCH("d","abc")), value_if_true, value_if_false)

and because you want 0 when there is an error (i.e. no match) the value_if_true is 0 and value_if_false is 1 where there is no error (i.e. a match)

=IF(ISERROR(SEARCH("d","abc")), 0, 1) 'returns 0 because of error in SEARCH
=IF(ISERROR(SEARCH("c","abc")), 0, 1) 'returns 1 because no error in SEARCH

Upvotes: 2

Related Questions