Count matches with two criteria

[Osvaldo Palmeiro]

Hi all.
Please, I would like to count via formula the number of occurrences based on two criteria: same points AND same ages for each couple.
The formula should not refer to a specific value of points or a specific age, nor should refers to the cell rows that holds those data, but should adopt the equalities of points and ages as criteria.
Appreciate any help.
Thanks in advance.

Cópia de Cópia de Ranking x5 2.0-1 V2.xlsm
	A	B	C	D	E	F	G	H	I
1	name1	name2	points	age		couples		count ocurrences (expected results)
2	John	Anne	any points xx	any age aa		John	Anne	2
3	Mike	Anne	any points ww	any age		Mike	Mary	3
4	John	Mary	any points	any age
5	Mike	Mary	any points yy	any age mm
6	John	Anne	any points xx	any age aa
7	Mike	Mary	any points	any age
8	John	Anne	any points bb	any age
9	Mike	Mary	any points yy	any age mm
10	John	Mary	any points	any age
11	Mike	Mary	any points yy	any age mm
12
Planilha1

 

[jasonb75]

Could you explain why row 8 is not yellow and row 7 is not blue?

 

[Osvaldo Palmeiro]

Hi, jason.
Thanks for reply.

Row 8 is not yellow because C8 doesn't match C2 and C6, also D8 doesn't match D2 and D6.

Row 7 is not blue because C7 doesn't match C5, C9 and C11, also D7 doesn't match D5, D9 and D11.

Hope it's clearer now.

Thanks again.

 

[jasonb75]

So using the yellow as an example, what is the criteria to determine that 'Points xx' and 'Age aa' are the correct entries to count?

If row 8 is not counted because it doesn't match the others then there needs to be a reason why the others are the correct ones to count, otherwise it will not be possible to isolate them.

 

[Osvaldo Palmeiro]

The criteria is the equality between points and between ages and not the values themselves. That is, for the yellow couple we should count how many times there are equality between points and between ages.
Something like If And(A:A = F2, B:B=G2) then count duplicates in column C if those duplicates rows are duplicates in column D too.

I've got it via macro, applying Filter using each couple as criteria and then counting duplicates in column C & duplicates in column D same rows.

But I would prefer a solution via formula, if possible.

Thanks

 

[jasonb75]

Ok, I'm kind of following. It's often easier to understand what you want than it is to explain it to someone else.

With a slight change to your example, should this count as 2 for yellow and 2 for green, or 4 for the John / Anne combination (or something different)?

Book1
	A	B	C	D
1	name1	name2	points	age
2	John	Anne	any points xx	any age aa
3	Mike	Anne	any points ww	any age
4	John	Mary	any points	any age
5	Mike	Mary	any points yy	any age mm
6	John	Anne	any points xx	any age aa
7	Mike	Mary	any points	any age
8	John	Anne	any points bb	any age yy
9	Mike	Mary	any points yy	any age mm
10	John	Anne	any points bb	any age yy
11	Mike	Mary	any points yy	any age mm
Sheet2

 

[Osvaldo Palmeiro]

I apologize for not being able to accurately pass you the criteria and for my screen shot not being clear enough..

Yes, the desired result would be 4 for that combination.

Many, many thanks for now.

 

[jasonb75]

This looks like it works correctly, I've tested with your original example and my edited version with both returning the correct expected results.

Book1
	A	B	C	D	E	F	G	H
1	name1	name2	points	age		couples		count ocurrences
2	John	Anne	any points xx	any age aa		John	Anne	2
3	Mike	Anne	any points ww	any age		Mike	Mary	3
4	John	Mary	any points	any age
5	Mike	Mary	any points yy	any age mm
6	John	Anne	any points xx	any age aa
7	Mike	Mary	any points	any age
8	John	Anne	any points bb	any age
9	Mike	Mary	any points yy	any age mm
10	John	Mary	any points	any age
11	Mike	Mary	any points yy	any age mm
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=ROWS(FILTER($A$2:$B$11,($A$2:$A$11=F2)*($B$2:$B$11=G2)))-IFERROR(ROWS(UNIQUE(FILTER($A$2:$D$11,($A$2:$A$11=F2)*($B$2:$B$11=G2)),0,1)),0)

 

[Osvaldo Palmeiro]

Hey, jason.

It works nicely.

No words to thank you enough. Amazing.

Many, many, many thanks. Have a nice week.