In Google Drive I have a sheet in which I frequently export my Spotify playlists. From this list I like to document artists I listen to.

In short: Column A holds all the artists occurring in my playlist. Column B has a list of artists that occur at least once in the entire sheet. I have used the UNIQUE function for this


What I'm trying to do is to make a list (in column C) of how many times each unique artist from column B occurs in column A.

I have looked for a similar question for quite some time and it seems to resemble this one. However it doesn't seem to do the trick.


In column C I use the formula: =COUNTIF(A2:A900; B2) and my intention for doing so was for C2 to compare value B2 with everything between A2 and A900. I would like to point out that my skills are very novice.

The only problem is that this formula can't be expanded (dragged) to other cells with only its last parameter (B2, B3, B4 etc) changing. That is why I was thinking of an array construction.

In screenshot below I've illustrated the problem that arises

enter image description here

  • Try to fix row number with dollar sign =COUNTIF(A$2:A$900; B2)
    – JohnSUN
    Aug 29, 2022 at 13:19

1 Answer 1


This formula fills all cells of column C without the need to drag.

=INDEX(IF(B2:B="",,COUNTIF(A2:A, B2:B)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.