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]

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))))

 

[KRice]

For these source data:

MrExcel_20240301.xlsx
	A	B	C	D	E	F	G
1		AB		CD	EF		x
2		AB		CD	EF		y
3		GH		KL	TY		z
4		GH		KL	TY
5		GH		KL	TY		y
6		GH		KL	TY
7		GH		KL	TY		x
8		AB		CD	EF		y
9		AB		CD	EF		q
10		GH		KL	TY		x
11		GH		KL	TY		y
12		GH		KL	TY		r
Songs in the Playlists

...have a look at this and let me know if these are the counts you want. I wasn't sure whether you wanted a simple confirmation that at least one x and one y were present, or whether you want the number of rows containing either an x or a y, provided at least one x and one y are present.

MrExcel_20240301.xlsx
	A	B	C	D
1
2	CatB	CatD	CatE	Count (x,y)
3	AB	CD	EF	3
4	GH	KL	TY	4
5	GH	KL	AB	0
mjones_tab

Cell Formulas
Range	Formula
D3:D5	D3	=LET(src,'Songs in the Playlists'!$B$1:$G$12,s,CHOOSECOLS(src,1,3,4,6),b,CHOOSECOLS(s,1),d,CHOOSECOLS(s,2),e,CHOOSECOLS(s,3),g,CHOOSECOLS(s,6),fres,FILTER(s,(b=A3)*(d=B3)*(e=C3),EXPAND("",1,COLUMNS(s),"")),lcol,CHOOSECOLS(fres,4),xs,SUM(--(lcol="x")),ys,SUM(--(lcol="y")),IF(AND(xs>0,ys>0),SUM(xs,ys),0))

Edit: I revised the formula slightly to trap an error if FILTER finds no match...so now an empty array is returned, which will lead to a count of 0.

 

[mjones]

Wow. I think this is the most complicated formula I've ever seen!

I definitely want to count the number of occurrences. I'll take action depending on the number.

I'm trying to count the number of pairs. Thus, in your results example, row 3 should be 1 occurrence of the x/y paring.

Note that the pairs will be sorted so you won't see, in your source data example, rows 8 and 9. And there will only ever be one x or one y for each pair in columns B, D & E, if that helps.

For me to test your formula, because it's so complicated, where do I replace the x's and y's with my real text; just where I see x's and y's in the formula? For example, x's could be "Easy Listening" and y's could be "Oldies (Pre 1970)". Yes, some have spaces and some have brackets. One even has a dash. Looking further, some have &, /, (, ), and '. If some of those characters are troublesome, with a bit of work, I could change the text.

Thank you very much for your time,

Michele

 

[Tetra201]

... The case below would count 1 for rows 1 and 2 matching the scenario, but rows 3 and 4 do not count...

The following simpler formula returns the expected results for the posted dataset:

=COUNTIFS(B:B,B1,D:D,D1,E:E,E1,G:G,IF(G1="x","y",IF(G1="y","x",CHAR(0))))

 

[mjones]

I tried it, replacing x and y and adding the other tab where I guessed it should be, like this:

=COUNTIFS('Songs in Playlists'!B:B,'Songs in Playlists'!B1,'Songs in Playlists'!D:D,'Songs in Playlists'!D1,'Songs in Playlists'!E:E,'Songs in Playlists'!E1,'Songs in Playlists'!G:G,IF('Songs in Playlists'!G1="Rock","80's",IF('Songs in Playlists'!G1="Rock","80's",CHAR(0))))

At least I didn't get an error. I got zero. Counting manually, it should be 63 so far.

Thanks again!

Michele

 

[KRice]

Michele, yes...those are the places to edit; however, the formula is counting the number of rows matching the BDE criteria and having either x or y in G. This version confirms that at least one x and y exist, and then takes the minimum of those two counts, which would be the number of pairs.

MrExcel_20240301.xlsx
	A	B	C	D	E	F	G
1		AB		CD	EF		Oldies (Pre 1970)
2		AB		CD	EF		Easy Listening/'Smooth' Jazz
3		GH		KL	TY		z
4		GH		KL	TY
5		GH		KL	TY		Easy Listening/'Smooth' Jazz
6		GH		KL	TY
7		GH		KL	TY		Oldies (Pre 1970)
8		AB		CD	EF		Easy Listening/'Smooth' Jazz
9		AB		CD	EF		q
10		GH		KL	TY		Oldies (Pre 1970)
11		GH		KL	TY		Easy Listening/'Smooth' Jazz
12		GH		KL	TY		r
Songs in the Playlists

If you don't want to hardwire the music genres directly into the formula, you could reference them like this:

MrExcel_20240301.xlsx
	A	B	C	D	E	F	G
1
2	CatB	CatD	CatE	Pair Count
3	AB	CD	EF	1		Oldies (Pre 1970)	Easy Listening/'Smooth' Jazz
4	GH	KL	TY	2		Oldies (Pre 1970)	Easy Listening/'Smooth' Jazz
5	GH	KL	AB	0		Oldies (Pre 1970)	Easy Listening/'Smooth' Jazz
mjones_tab

Cell Formulas
Range	Formula
D3:D5	D3	=LET(src,'Songs in the Playlists'!$B$1:$G$12, s,CHOOSECOLS(src,1,3,4,6), b,CHOOSECOLS(s,1), d,CHOOSECOLS(s,2), e,CHOOSECOLS(s,3), g,CHOOSECOLS(s,6), fres,FILTER(s,(b=A3)*(d=B3)*(e=C3),EXPAND("",1,COLUMNS(s),"")), lcol,CHOOSECOLS(fres,4), xs,SUM(--(lcol=F3)), ys,SUM(--(lcol=G3)), IF(AND(xs>0,ys>0),MIN(xs,ys),0))

 

[KRice]

Here is another version that determines the x and y pair counts using SUMPRODUCT and relying on this dataset:

MrExcel_20240301.xlsx
	A	B	C	D	E	F	G
1		AB		CD	EF		x
2		AB		CD	EF		y
3		GH		KL	TY		z
4		GH		KL	TY
5		GH		KL	TY		y
6		GH		KL	TY
7		GH		KL	TY		x
8		AB		CD	EF		y
9		AB		CD	EF		q
10		GH		KL	TY		x
11		GH		KL	TY		y
12		GH		KL	TY		r
Songs in the Playlists2

MrExcel_20240301.xlsx
	A	B	C	D
6	CatB	CatD	CatE	Pair Count
7	AB	CD	EF	1
8	GH	KL	TY	2
9	GH	KL	AB	0
mjones_tab

Cell Formulas
Range	Formula
D7:D9	D7	=LET(src,'Songs in the Playlists2'!$B$1:$G$12,s,CHOOSECOLS(src,1,3,4,6),b,CHOOSECOLS(s,1),d,CHOOSECOLS(s,2),e,CHOOSECOLS(s,3),g,CHOOSECOLS(s,4), resx, SUMPRODUCT((b=A7)*(d=B7)*(e=C7)*(g="x")), resy, SUMPRODUCT((b=A7)*(d=B7)*(e=C7)*(g="y")), IF(AND(resx>0,resy>0),MIN(resx,resy),0))

You can edit the "x" and "y" to reflect the desired search phrases.

 

[mjones]

This is confusing.

Results should look like this:
A B
Easy Listening & Oldies (Pre 1970) 63
Easy Listening & 80's 58
Easy Listening & 90's 21
Easy Listening & 70's 78
Rock & Oldies (Pre 1970) 85
Rock & 80's 68
and so on

Thus, there are 63 songs (where their rows have matching columns B, D & E match) and where their columns G have both Easy Listening and Oldies (Pre 1970) where Easy Listening and Oldies (Pre 1970) are in different rows. No need to check that Easy Listening or Oldies (Pre 1970) occur for the same song more than once because they won't.

The data is arranged so that each row is a song (B, D & E will tell their uniqueness) and an associated playlist (types of music like rock or easy listening (28 possibilities) and only one of 4 decades). Thus, if a song is in 4 types of music and one decade, the song will have 5 rows.

I hope this helps and do truly thank you for possibly saving me oodles of time,

Michele

 

[KRice]

It's difficult to advise without seeing more data. What formula are you using that delivers the results 63, 58, etc.?