I am trying to do a counting of the number of strings that match a given string in microsoft excel. For example, if I have

String to be matched - 'apple'

and an array as follows - 'apple' 'banana' 'apple' 'pear' 'mango' 'apple'

I want excel to return 3 as apple occurs 3 times. I am using COUNTIF for doing this, but cannot figure out how to give condition such that I can say ith element = given string (similar to how we do in writing code on c/java). Any help is appreciated.

  • your "array" has elements in different cells? Then use COUNTIF ...or it's everything is in a single cell?
    – danicotra
    Jun 29, 2017 at 17:56
  • They are in different cells Jun 29, 2017 at 18:13
  • yass already answered you then
    – danicotra
    Jun 29, 2017 at 18:22

2 Answers 2


You mean your Data in column A (A1:A6),

Column A  

In B1 write:



  • Could you give link to a resource that explains how referencing in such situations works. Eg: if I want to calculate the no. of cells empty in given range. Jun 29, 2017 at 18:23
  • =COUNTIF(A1:A9,"") will count empty cells "" countif
    – yass
    Jun 29, 2017 at 18:42

COUNTIF() is great if the data is spread out over several cells, but if we have:

enter image description here

Then use:

  • ...Damn you smart guy! ;-D
    – danicotra
    Jun 29, 2017 at 18:24
  • @danicotra ...........I did not invent this.........all I did was remember it....... Jun 29, 2017 at 18:25
  • I know yours is not the answer here but... I couldn't not upvote it anyway! :-)
    – danicotra
    Jun 29, 2017 at 18:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .