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]

Oh good!...so those numbers make sense to you? I'll give some thought to a more efficient way.

 

[mjones]

To confirm, yes numbers are correct - formula works! The speed is just unworkable and most likely will worsen when the source data is doubled. I didn't know a formula could slow Excel so much but it makes sense. Crossing fingers. Good night.

 

[KRice]

Good...about the numbers being correct. And yes, some formulas can make a worksheet sluggish. I've reviewed your earlier comment---I guess there is no need to sum the number of rows where B,D,E match with one of the G=x or G=y terms because you've said that there would ever only be one or none with an x, and one or none with a y. And that appears to be the case, so I think there are some other approaches to try out. Just to confirm then, for any chosen B,D,E entry, you're expecting the number of pairs (after considering the G=x and G=y criteria) will be either 0 or 1....and you want the sum of those 0's and 1's for the entire set of B,D,E on the 'Songs...' worksheet?

 

[KRice]

I think this idea is much better. We start by defining the source data "src" and ensure that the row labels that consist of two Opt. Playlists categories use exact spelling for matching purposes, and the two categories use a colon space delimiter. The formula extracts the first of those (to the left of ": ") as the x match target, and the second of those (to the right of the delimiter) as the y match target. For convenience, we specify variable "g" as the data in the 6th column of src data...col G. Next we perform a simple concatenation on the B, D, E columns (cols 1, 3, 4 of src dat), separating each with a pipe character (|)...and we call this single column array "bde". Then we filter the 5000+ row bde array to obtain only those entries where g=x (the G column matches the 1st category---Easy Listening for example). This trims the 5000+ array down to several hundred entries. And then we take only unique entries in this list. Strictly speaking, this UNIQUE operation is probably not necessary since there should be no more than one of any bde entry whose g=x. This UNIQUE/FILTER bde array is called "fsx", short for filtered source data with "x" matching.
Finally, we return to the full length "g" array (5000+ entries in it) and perform a logical test...where does g=y (the 2nd category..."Oldies (Pre 1970)" for example)? We also perform a MATCH, checking to see if each item in the 5000+ bde array matches any one of the unique, filtered fsx entries (where the x match is already known to exist)...ISNUMBER converts the MATCH result to TRUE or FALSE, and both arrays are multiplied together inside SUMPRODUCT to determine which array index positions satisfy both criteria, resulting in either a 0 or 1 for each array position. SUMPRODUCT then sums the column. This should be much faster than the earlier approach.

Michele's iTunes Music_revKR.xlsx
	A	B
22	Easy Listening	300
23	Easy Listening: Oldies (Pre 1970)	34
24	Easy Listening: 70's Music	28
25	Easy Listening: 80's	18
26	Easy Listening: 90's & Newer	12
Playlist Totals

Cell Formulas
Range	Formula
B22	B22	=COUNTIF('Songs in Playlists'!G$2:G$5748,A22)
B23:B26	B23	=LET(src,'Songs in Playlists'!B$2:G$5748, x,TEXTBEFORE(A23,": "), y,TEXTAFTER(A23,": "), g,INDEX(src,,6), bde, INDEX(src,,1) & "|"& INDEX(src,,3) & "|" & INDEX(src,,4), fsx,UNIQUE(FILTER(bde,g=x,"")), SUMPRODUCT((g=y) * ISNUMBER(MATCH(bde,fsx,0))))

 

[mjones]

Now you have it! Formula works and at a fast speed!

A thing to note is that column G has a drop down from a list and thus won't have a typo. Plus, you are correct in that any song will have only unique items in column G. If I put more than one of the same playlist in column G (e.g. easy listening or 80's), other sums will give me a warning and I'll find it that way, i.e. sums in iTunes won't match those in the spreadsheet because iTunes won't let you put a song in more than one playlist.

I cannot thank you enough. I do hope you enjoyed the tricky question. All the best!

Michele

 

[KRice]

Michele, I noticed that about column G...a good idea to use a drop down list for consistency. And in testing out the formula--and making some targeted changes to the source table to confirm counting accuracy--I saw the TRUE/FALSE flags alert. So I think you could safely avoid performing the UNIQUE operation, as it would not actually eliminate anything because of the other checks. In that case, a minor tweak:

=LET(src,'Songs in Playlists'!B$2:G$5748, x,TEXTBEFORE(A23,": "), y,TEXTAFTER(A23,": "), g,INDEX(src,,6),
bde,INDEX(src,,1)&"|"&INDEX(src,,3)&"|"&INDEX(src,,4), fsx,FILTER(bde,g=x,""), SUMPRODUCT((g=y)*ISNUMBER(MATCH(bde,fsx,0))))

You're very welcome...I appreciate your patience working through the formula evolution.

 

[mjones]

New formula works and is fast. I think I should be thanking you for your patience. Thank you!

 

[mjones]

Hi again,

Thanks for the very useful formula above – use it all the time.

It counts the number of occurrences with a combination of two things being x and y. I tried and failed to make it count combinations of three things. Can someone kindly help with this?

There are over 16,000 rows in my spreadsheet now. The formula above looks to see that columns B, D & E match and then counts how many of those rows have both things in column G like Dance (Fast) and 70’s, which I put in the cell to the left of the formula, e.g. Dance (Fast): 70’s with the : separating the two things to test for.

I like to check for three things in column G like Dance (Fast), 70’s and Jazz. I’m not sure which other separator like the colon I can use. For example, Dance (Fast): 70’s# Jazz.

Thank you very much,

Michele

 

[KRice]

Glad to hear the formula is working well. About this extension to three matches...I'll suggest using the same delimiter )...a colon space...and then using TEXTSPLIT to split the input string (e.g., Dance (Fast): 70’s: Jazz) into Dance (Fast) and 70’s and Jazz. So we'll have "x" assigned to the 1st of those, "y" assigned to the 2nd, and "z" assigned to the 3rd. But how are the matches to be performed? Where is the 3rd item (e.g., Jazz) to match?...is it column N (Genre) in the 'Songs in Playlists' worksheet? And are all three matches supposed to be logical AND conditions (meaning all three must be true at the same time)?

 

[mjones]

Hi KRice,

Good questions. Yes, all three must be present. And the third item is also in column G (Genre) in the 'Songs in Playlists' worksheet with the other two.

The formula is used in the 'Playlist Totals' worksheet, i.e. a different tab. It's in column B and the Dance (Fast): 70's (or whatever is being tested for) is in column A to the left of the formula.

Thank you again!

Michele