# How do I find the appearance rate of one list of cells in another list of cells?

I imported all of the data from my iTunes library into a Google sheet for my own amusement, and I want to know by album how many songs from each album I thought were good enough to put in my "favorites" playlist. So if from an album of 10 songs I like 3 of them enough to put in my playlist, I would want that album to show a value of 0.3.

The way I have my data set up is that there's a column for song title, duration, album, artist, etc, and additionally a column for "present in playlist". I already used some formulas to compare my favorites playlist to my general library to find any duplicates, so essentially if a song is both in my general library AND in my favorites playlist, I put an "X" in the "present in playlist" column. It's functioning as a binary answer at this point, either yes or no.

Given that setup, is there a formula I can use where I can plug in a string, like "Billy Joel's Greatest Hits" and have it return a value indicating the percentage of songs in that album that have an "X" in the "present in playlist" column?

Sorry if my title doesn't actually represent the question I'm asking here. I rewrote it like 5 times because I couldn't think of a concise or even accurate way of distilling it any further.

EDIT: adjusted my final question to reflect a percentage rather than a sum.

• Can you please provide a copy of your spreadsheet? It's much easier to help you if we can actually see the data your working with. Also, fun idea of analyzing the music you listen to. Got to give that a try. Apr 7 at 23:36
• Erik Tyler kindly reached out to help me last night and the formula he provided is exactly what I was looking for. Thanks everyone for your contribution. Apr 8 at 13:20

``````=COUNTIF(FILTER('Song List'!C:C,'Song List'!D:D = "X"),C4)/(COUNTIF('Song List'!\$D:\$D,"X"))
I have an alternative formula for counting the total amount of songs in the album using `COUNTA()` instead of `COUNTIF()`, though I'm not actually sure whether it's faster. I think it's faster since it's not comparing the values, but I could be wrong.
``````=COUNTIF(FILTER('Song List'!C:C,'Song List'!D:D = "X"),C4)/(COUNTA('Song List'!\$D:\$D)-1)