1

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.

Improve this question
2
  • 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.
    – David Tan
    Apr 7, 2022 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.
    – Arnold Cleeton
    Apr 8, 2022 at 13:20

1 Answer 1

Reset to default
0

link to my copy

Check the summary tab. It simply divides the number of exact matches of the artist's name with the total number of songs in the playlist. Though after skimming through some of your songs, I notice that there are some artist names that are colabs. If you want to include colabs in that count, might I suggest that you look into this link that uses regex to count the matches even if within a cell.

Here is the full formula.

=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)
Improve this answer

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