Countifs with uniques and wildcards and OR function

[Country_Calc]

I am trying to count unique records that involve an OR function and other criteria.

Here is the query to be answered.

How many Names have either *MD Anderson*" OR "*Anderson*" have City as "Houston" and Variable of greater than 0.

I want to do something like this, but it does not work and does not take Unique result into account.

=SUM(COUNTIFS(C2:C8,"Houston",D2:D8,">0",B2:B8,{"*"&F2&"*","*"&G2&"*"}))

Please let me know if this is possible.

Unique ID Number	Name	City	Variable		Name Query 1	Name Query 2	Country	Desired Result
6680	M.D. Anderson Internacional	Madrid	1		MD Anderson	Anderson	Houston	2​
7632	MD Anderson Cancer Center	Houston	6
8587	MD Anderson Cancer Center	Madrid	0
8614	MD Anderson Cancer Center	Madrid	0
10076	M.D. Anderson Cancer Center	Houston	0
10077	M.D. Anderson Cancer Center	Houston	1
10078	MD Anderson Cancer Center	Houston	0

 

[Sufiyan97]

Try

Book5
	A	B	C	D	E	F	G	H	I	J
1	Unique ID Number	Name	City	Variable		Name Query 1	Name Query 2	Country	Desired Result
2	6680	M.D. Anderson Internacional	Madrid	1		MD Anderson	Anderson	Houston	2
3	7632	MD Anderson Cancer Center	Houston	6
4	8587	MD Anderson Cancer Center	Madrid	0
5	8614	MD Anderson Cancer Center	Madrid	0
6	10076	M.D. Anderson Cancer Center	Houston	0
7	10077	M.D. Anderson Cancer Center	Houston	1
8	10078	MD Anderson Cancer Center	Houston	0
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS(B2:B8,"*"&G2&"*",C2:C8,H2,D2:D8,">0")

 

[JamesCanale]

This might do OR well if your name queries are where one doesn't contain the other.

MrExcelPlayground15.xlsx
	A	B	C	D	E	F	G	H	I
1	Unique ID Number	Name	City	Variable		Name Query 1	Name Query 2	Country	Desired Result
2	6680	M.D. Anderson Internacional	Madrid	1		MD Anderson	Anderson	Houston	2
3	7632	MD Anderson Cancer Center	Houston	6
4	8587	MD Anderson Cancer Center	Madrid	0
5	8614	MD Anderson Cancer Center	Madrid	0
6	10076	M.D. Anderson Cancer Center	Houston	0
7	10077	M.D. Anderson Cancer Center	Houston	1
8	10078	MD Anderson Cancer Center	Houston	0
Sheet14

Cell Formulas
Range	Formula
I2	I2	=LET(z,FILTER(C2:C8,D2:D8>0),a,FILTER(B2:B8,D2:D8>0),b,ISNUMBER(SEARCH(F2,a)),c,ISNUMBER(SEARCH(G2,a)),d,ISNUMBER(SEARCH(H2,z)),e,SIGN(b+c)*d,SUM(e))

 

[Country_Calc]

Try

Book5
	A	B	C	D	E	F	G	H	I	J
1	Unique ID Number	Name	City	Variable		Name Query 1	Name Query 2	Country	Desired Result
2	6680	M.D. Anderson Internacional	Madrid	1		MD Anderson	Anderson	Houston	2
3	7632	MD Anderson Cancer Center	Houston	6
4	8587	MD Anderson Cancer Center	Madrid	0
5	8614	MD Anderson Cancer Center	Madrid	0
6	10076	M.D. Anderson Cancer Center	Houston	0
7	10077	M.D. Anderson Cancer Center	Houston	1
8	10078	MD Anderson Cancer Center	Houston	0
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS(B2:B8,"*"&G2&"*",C2:C8,H2,D2:D8,">0")

The example I posted was not ideal because "*anderson*" would cover both wildcard text queries. Here is a better one for what I am trying to do.

Query is for "*University of California at Los Angeles*" or "*UCLA*"

Because 1 record has both terms in the same cell, I need to add in Unique somehow.

Unique ID Number	Name	City	Variable		Name Query 1	Name Query 2	Country	Desired Result
6680	University of California at Los Angeles (UCLA)	Los Angeles	1		University of California at Los Angeles	UCLA	Los Angeles	3
7632	UCLA	Los Angeles	6
8587	University of California at Los Angeles	Los Angeles	0
8614	University of California (UCLA)	Los Angeles	1

 

[Sufiyan97]

Try

Book5
	A	B	C	D	E	F	G	H	I	J
1
2	Unique ID Number	Name	City	Variable		Name Query 1	Name Query 2	Country	Desired Result
3	6680	University of California at Los Angeles (UCLA)	Los Angeles	1		University of California at Los Angeles	UCLA	Los Angeles	3
4	7632	UCLA	Los Angeles	6
5	8587	University of California at Los Angeles	Los Angeles	0
6	8614	University of California (UCLA)	Los Angeles	1
7
8
9
10
11
Sheet3

Cell Formulas
Range	Formula
I3	I3	=SUMPRODUCT(ISNUMBER(MATCH("*"&G3&"*",$B$3:$B$9,0))*ISNUMBER(MATCH(H3,C3:C9,0))*(D3:D9>0)*ISNUMBER(MATCH("*"&F3&"*",$B$3:$B$9,0)))

 

[Sufiyan97]

Have you tried formula in post #3?

 

[Country_Calc]

JamesCanale's solution worked for all my needs. Thanks so much.​