Tricky counting formula

[mjones]

Hi All,

At > 6000 rows, it’s impossible to manually count so I hope a formula genius out there can help.

All cells are general format with text.

Count how many situations occur with rows with the same text in columns B, D & E that also have a row with x in column G and another row with y in column G.

The case below would count 1 for rows 1 and 2 matching the scenario, but rows 3 and 4 do not count because even though B, D & E match, there are not two rows where one has an x and one has a y in column G.

B D E G
1 AB CD EF x
2 AB CD EF y
3 GH KL TY z
4 GH KL TY y

The above is in the 'Songs in Playlists'! tab, but the count/answer will be in another tab.

Thank you for considering my formula,

Michele

 

[KRice]

Refresh my memory, please...this is being done to identify the number of duplicated entries, correct?...where a record for a given song might classify it as 70's Music, and another record for the same song might classify it differently, perhaps Easy Listening?...and then a third record might classify it as Jazz. So we might find three rows for the same song, and the column G classifiers would be different?...This set of three would count as "1"?

 

[mjones]

Hi Krice,

Sorry for the delay. Here is a sample of a song with its genres (Opt. Playlist) in column G.

This is being done to count the number of songs with given genre groups to determine if there are enough songs (by knowing the count) to make a separate playlist. For example, I currently have a playlist with 821 rock songs and one with 873 70’s songs that I consider too big so I might split rock by decades (still too big) or Dance (Fast), which has 419 songs into separate Disco and Jazz playlists.

Basically, the count helps me make decisions.

Yes, I would never have column G for the same song to have the same text, always unique and from a dropdown so no typos. And yes, this set of three would count as 1.

I hope that helps and I truly truly appreciate your help. I’m considered good at Excel by most, but this type of formula is way beyond me, and in my estimation, only achieved by an elite group of people like yourself.

Best regards,

Michele

 

[KRice]

Michele,
Thanks for the explanation. I've considered a few ways to do this, but was concerned they might cause the workbook to become sluggish. The approach here is a direct extension of the original that seems to be working well. We define a new variable xyz that is a horizontal array of the three components that might be found in column A (e.g., Easy Listening: 70's Music: Rock), each component using the same colon space delimiter. If a component is missing (e.g., Easy Listening: 70's Music), the xyz formula treats the missing component as a blank. Then for convenience, we extract each component and assign them to easier-to-follow variables: x, y, and z (so x is Easy Listening, y is 70's Music, and z is Rock). The same bde variable is used as before...which is nothing more than a concatenated text string for each row of the source data consisting of the columns B, D, and E entries.

Then we use sequential filtering:

• bdex determines which bde items satisfy the g=x condition.
• bdexy determines which bde items that belong to bdex also satisfy the g=y condition
• bdexyz determines which bde items that belong to bdey (and therefore also belong to bdex) also satisfy the g=z condition.

If you want a list of the bde items for any of these conditions, the last argument in the LET statement should reflect that (e.g., bdexyz will show the bde results when the x, y, and z conditions are met). And if you want a count of those items, then the variable name is wrapped within a COUNTA function. The example below shows both of these options. Your request is addressed with the COUNTA version, but you might find the bde lists helpful to offer details on where to find those songs having multiple classifications. Give this a try and let me know if you encounter difficulties or errors...I haven't tested it extensively.

Michele's iTunes Music_revKR_20240730.xlsx
	A	B					G	H
24	Easy Listening: 70's Music: Rock	3					After The Gold Rush|Neil Young|Neil Young: Decade [Disc 1]	After The Gold Rush|Neil Young|Neil Young: Decade [Disc 1]
25							First Cut Is the Deepest (The)|Rod Stewart|Greatest Hits, Vol. 1	After the Love Has Gone|Earth, Wind & Fire|Music for Small Rooms
26							That's The Way|Led Zeppelin|Almost Famous (Music From The Motion Picture)	Afternoon Delight|Starland Vocal Band|The Best Pop Album Of The 70's... ever! [Disc 2]
27								Air That I Breathe (The)|The Hollies|All-Time Greatest Hits
28								Any Colour You Like|Pink Floyd|The Dark Side Of The Moon (20th Anniversary Edition - Digitally Remastered Sept. 1992)
29								Broken Hearted Me|Anne Murray|Greatest Hits - Anne Murray
30								Call On Me|Chicago|The Ultimate Collection
31								Calling Occupants of the Interplanetary Craft|Carpenters|40 / 40: Best Selection [Disc 1]
32								Calypso|John Denver|John Denver: Greatest Hits Vol. 2
33								Can't Get It out of My Head|Electric Light Orchestra|ELO's Greatest Hits
34								Deacon Blues|Steely Dan|Aja
35								Father and Son|Cat Stevens|Tea for the Tillerman
36								Fire|Bruce Springsteen & The E Street Band|Live 1975-85 [Disc 1]
37								First Cut Is the Deepest (The)|Rod Stewart|Greatest Hits, Vol. 1
38								Fooled Around and Fell in Love|Elvin Bishop|Goin' South [Disc 2]
39								For All We Know|Carpenters|40 / 40: Best Selection [Disc 1]
40								Goodbye to Love|Carpenters|40 / 40: Best Selection [Disc 2]
41								Goodbye Yellow Brick Road|Elton John|To Be Continued... [Disc 2]
42								Guitar Man (The)|Bread|Anthology of Bread
43								Home At Last|Steely Dan|Aja
44								How Can You Mend a Broken Heart|Bee Gees|Trafalgar
45								How Deep Is Your Love|Bee Gees|Saturday Night Fever
46								My Love|Paul McCartney & Wings|All The Best!
47								So Far Away|Carole King|Artist's Choice: Sheryl Crow
48								That's The Way|Led Zeppelin|Almost Famous (Music From The Motion Picture)
49								Tryin' To Get The Feeling Again|Barry Manilow|Barry Manilow: Greatest Hits Volume 1
50								You Sexy Thing|Hot Chocolate|The Best Songs Of All Time, Vol.1: Funk, Soul, R&B
51								You're So Vain|Carly Simon|Time 100 (The Music of Our Lives 1960-1980) [Disc 2]
Playlist Totals

Cell Formulas
Range	Formula
B24	B24	=LET(src,'Songs in Playlists'!B$2:G$5748, g,INDEX(src,,6), xyz,TEXTSPLIT(A24,": "), x,INDEX(xyz,,1), y,IFERROR(INDEX(xyz,,2),""), z,IFERROR(INDEX(xyz,,3),""), bde,INDEX(src,,1) & "|"& INDEX(src,,3) & "|" & INDEX(src,,4), bdex,FILTER(bde,g=x,""), bdexy,FILTER(bde,(g=y)*ISNUMBER(MATCH(bde,bdex,0)),""), bdexyz,FILTER(bde,(g=z)*ISNUMBER(MATCH(bde,bdexy,0)),""), COUNTA(bdexyz) )
G24:G26	G24	=LET(src,'Songs in Playlists'!B$2:G$5748, g,INDEX(src,,6), xyz,TEXTSPLIT(A24,": "), x,INDEX(xyz,,1), y,IFERROR(INDEX(xyz,,2),""), z,IFERROR(INDEX(xyz,,3),""), bde,INDEX(src,,1) & "|"& INDEX(src,,3) & "|" & INDEX(src,,4), bdex,FILTER(bde,g=x,""), bdexy,FILTER(bde,(g=y)*ISNUMBER(MATCH(bde,bdex,0)),""), bdexyz,FILTER(bde,(g=z)*ISNUMBER(MATCH(bde,bdexy,0)),""), bdexyz )
H24:H51	H24	=LET(src,'Songs in Playlists'!B$2:G$5748, g,INDEX(src,,6), xyz,TEXTSPLIT(A24,": "), x,INDEX(xyz,,1), y,IFERROR(INDEX(xyz,,2),""), z,IFERROR(INDEX(xyz,,3),""), bde,INDEX(src,,1) & "|"& INDEX(src,,3) & "|" & INDEX(src,,4), bdex,FILTER(bde,g=x,""), bdexy,FILTER(bde,(g=y)*ISNUMBER(MATCH(bde,bdex,0)),""), bdexyz,FILTER(bde,(g=z)*ISNUMBER(MATCH(bde,bdexy,0)),""), bdexy )
Dynamic array formulas.

 

[mjones]

My only comment is 'perfect' with added bonuses!! What a time saver. Thank you so very much!!

 

[KRice]

You're welcome...happy to help. Post back if you encounter any issues.