Formula to count unique values based on multiple criteria, including 1 column where at least 3 of 5 criteria should be found?

[peeblescd]

Hello everyone, I'm not sure what kind of function of is needed here, though here's a sample of the data and what I'm trying to do:

Session 1	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
Session 2	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
Session 4	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
Session 1	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
Session 2	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
Session 3	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
Session 5	SME Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian

The formula should return a count of how many times an email address in column D meets these criteria:

• At least 3 of the following 5 values are found for column A: "Session 1", "Session 2", "Session 3", "Session 4", and/or "Session 5"
• Column B is "Startup Arabic"
• Column C is "Yes"
• Column D is "Turkey"
• Column E is "Male"
• Column F is "Syrian"

So according to the above data sample, the formula should return "1" since "mickeymouse@aol.com" met the desired criteria just once.

Using COUNTIFS would be ideal in this case if it weren't for column A; I can't figure out how to tell the formula to look for at least 3 of the 5 session numbers. Does anyone have ideas? Thank you in advance!

 

[mart37]

Excel 365 have functions like AGGREGATE - FILTER - UNIQUE. Maybe you can use those functions.

 

[peeblescd]

Excel 365 have functions like AGGREGATE - FILTER - UNIQUE. Maybe you can use those functions.

Thank you, though I'm looking more for an example of a formula that might work. I've read about FILTER and UNIQUE and don't understand how to write the function for this particular case.

 

[jasonb75]

At least 3 of the following 5 values are found for column A: "Session 1", "Session 2", "Session 3", "Session 4", and/or "Session 5"

Are duplicate rows possible? e.g. "Session 1" appearing twice for a single email address, with all of the other columns meeting the specified criteria?

Are there any other possible entries in column A that would not meet the criteria of Session 1 to 5?

 

[peeblescd]

Are duplicate rows possible? e.g. "Session 1" appearing twice for a single email address, with all of the other columns meeting the specified criteria?

Are there any other possible entries in column A that would not meet the criteria of Session 1 to 5?

Yes, duplicate rows are possible, and there are no other possible entries in column A--only the 5 listed entries I mentioned exist there.

 

[jasonb75]

This appears to work correctly, but I have only done a very quick test.

If your version of excel is missing any of the functions used then this could prove difficult. Whilst not impossible, I would expect some extremely long formulas.

Book1
	A	B	C	D	E	F	G	H	I
1	Session 1	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian		Startup Arabic
2	Session 2	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian		Yes
3	Session 4	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian		Turkey
4	Session 1	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian		Male
5	Session 2	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian		Syrian
6	Session 3	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian		1
7	Session 5	SME Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
Sheet2

Cell Formulas
Range	Formula
I6	I6	=LET(a,INDEX(UNIQUE(FILTER(A1:D7,(B1:B7=I1)*(C1:C7=I2)*(E1:E7=I3)*(F1:F7=I4)*(G1:G7=I5),"")),,4),c,SEQUENCE(1,ROWS(a)),SUM(IF(FREQUENCY(IF(MATCH(a,a,0)=c,c),c)>=3,1)))

 

[Eric W]

Here's one way without using the newer functions:

Book2
	A	B	C	D	E	F	G	H	I
1	Session	Title	Attended	email	Country	Gender	Nationality		# of matching email addresses
2	Session 1	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian		2
3	Session 2	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
4	Session 4	Startup Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
5	Session 1	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
6	Session 2	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
7	Session 3	Startup English	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
8	Session 5	SME Arabic	Yes	mickeymouse@aol.com	Turkey	Male	Syrian
9	Session 6	Startup English	No	Minniemouse@aol.com	Greece	Female	Syrian
10	Session 1	Startup Arabic	Yes	abc@aol.com	Turkey	Male	Syrian
11	Session 2	Startup Arabic	Yes	abc@aol.com	Turkey	Male	Syrian
12	Session 5	Startup Arabic	Yes	abc@aol.com	Turkey	Male	Syrian
13
Sheet7

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT((MMULT(COUNTIFS(A:A,"Session "&{1,2,3,4,5},B:B,"Startup Arabic",C:C,"Yes",E:E,"Turkey",F:F,"Male",G:G,"Syrian",D:D,D2:D12),{1;1;1;1;1})>2)*(MATCH(D2:D12,D2:D12,0)=ROW(D2:D12)-ROW(D2)+1))

The formula is long, but not too terrible, but it is tricky.

I added some extra rows with additional test data, because I wasn't entirely sure what you were asking. In this list there are 2 emails that have all the criteria you want, and one that doesn't.

 

[jasonb75]

At least 3 of the following 5 values are found for column A: "Session 1", "Session 2", "Session 3", "Session 4", and/or "Session 5"

@Eric W, yours is still counting where there are less than 3 of the listed values found when there are duplicate rows. e.g. with the same session number in A2 and A3.

 

[Eric W]

True enough.

@Eric W, yours is still counting where there are less than 3 of the listed values found when there are duplicate rows. e.g. with the same session number in A2 and A3.

True enough. I didn't see any duplicate rows in the sample data, so I figured I'd cross that bridge if it came up.

Incidentally, if I'm reading your formula correctly, you don't specifically look for "Session 1", "Session 2", etc. in your formula, you just look for 3 unique values. So if there's something like "Intro" or "Q&A Session" in column A, you'd get incorrect results.

 

[jasonb75]

I didn't see any duplicate rows in the sample data, so I figured I'd cross that bridge if it came up.

So if there's something like "Intro" or "Q&A Session" in column A, you'd get incorrect results.

See posts 4 & 5.