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]

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.?

 

[mjones]

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

There wasn't really a formula. Long story short, I filtered a lot, copied to another tab, compared some columns in seven different formulas, then then counted the 1's.

Here's an image with the columns that matter (the columns go to W).