COUNTIF: a powerful filtering function

COUNTIF: a powerful filtering function

excel tips 3

Excel has a great filtering ability which allows you to filter down by multiple criteria . This simple function will search for instances of text in multiple cells even if the text ‘string’ is sitting within other text and give you a total number of occurrences.

Let’s say I have a column of job vacancies and I want to know how many of them contain the word ‘Psychology’.

list2

The COUNTIF function has two arguments. It looks in the cells in its first argument for instances of a given text element you specify as its second argument, ‘psychology’, Because it is text it should have double quotes around it:

formula1a

But, the result of zero here is incorrect. Why? Because the formula is set up to look for the number of instances of the word ‘psychology’ as the only word in the cell and there are no vacancies just for the word psychology.

To make the formula look within the text of every cell for the target word you should use the wildcard asterisk character *, in fact two. Depending where you place them, wildcard characters accept  literally ‘within anything before OR anything after’. Here is the finished formula and the result is 8.

formula1

And, like many Excel functions, it is not case sensitive so ‘psychology’ is counted the same as ‘PsYcHoloGy’.

 


2 comments on “COUNTIF: a powerful filtering function

  • Pete AKA PedanticPete

    “But, the result of zero here is incorrect.”

    No it is correct ! … as you say “[...] number of instances of the word ‘psychology’ as the only word in the cell and there are no vacancies just for the word psychology.”
    which is Zero.

    The formula was wrong, or lets say, not fit for purpose, then when the Formula is corrected with the wildcards the formula is correct and we get the result that we wanted.

    enjoying having lttle sound bits on excel, more please :)

Leave a comment

Your email address will not be published.

Required fields are marked with required


required

required

required