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!

 

[Eric W]

This takes care of the duplicate row issue:

=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)>0),{1;1;1;1;1})>2)*(MATCH(D2:D12,D2:D12,0)=ROW(D2:D12)-ROW(D2)+1))

 

[peeblescd]

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

Thank you very much for setting this up! I haven't seen a formula quite like this, so I have 2 questions for you. What do the letters "a" and "c" represent? And if I would like to search for text strings instead of using the index in this formula, could I do that? I believe this function would look something like this but with the index removed?:

=LET(a,INDEX(UNIQUE(FILTER(A1:D7,(B1:B7="Startup Arabic")*(C1:C7="Yes")*(E1:E7="Turkey")*(F1:F7="Male")*(G1:G7="Syrian"),"")),,4),c,SEQUENCE(1,ROWS(a)),SUM(IF(FREQUENCY(IF(MATCH(a,a,0)=c,c),c)>=3,1)))

 

[jasonb75]

What do the letters "a" and "c" represent?

Those are names applied to sections of the formula by using the LET function so that it is not necessary to repeat the same calculations multiple times.
'a' represents the section,

INDEX(UNIQUE(FILTER(A1:D7,(B1:B7="Startup Arabic")*(C1:C7="Yes")*(E1:E7="Turkey")*(F1:F7="Male")*(G1:G7="Syrian"),"")),,4)

which would be repeated 3 times if we didn't use LET. 'c' is used in a similar way.

if I would like to search for text strings instead of using the index in this formula

I'm not sure what you mean by that, INDEX is only used to isolate the email address column once the duplicates have been filtered out.

Have you looked at the alternative formula that @Eric W has put considerable effort into? That may be something that you're more familiar with.