COUNTIF only counting for unique entries

[RodneyC]

I need to modify this formula =COUNTIF(WO!G:G,"*BHM*") so that it also looks in WO!A:A for unique entries and only reports back the total number of times some form of BHM appears in column G but only counts it once for each unique entry in column A.

For example if

WO!A:A contains the value banana 147 times and there are 36 times that corresponding value in WO!G:G contain the value BHM
WO!A:A contains the value grape 21 times and there are 19 times that corresponding value in WO!G:G contains the value BHM

then the results of the formula would show 2

Thanks in advance

 

[RodneyC]

One addition... It should only count if WO!M:M is greater than 0

Thanks again

 

[AhoyNC]

You could try something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Also, you can't use the whole columns as a reference or you will get an error msg "Out of Resources".
I used rows 1 to 500,000 in this example.

Book2
	A					F	G						M	N	O	P
1	Banana						BHM						1		Count	2
2	Banana						BHM						2
3	Banana						RRR						0
4	Banana						BHM						0
5	grape						BHM						1
6	grape						ZZZ						0
7	grape						BHM						0
8	Banana						BHM						0
9	Apple						BHM						0
10
Sheet1

Cell Formulas
Range	Formula
P1	P1	=SUM(IF(FREQUENCY(IF(G1:G500000="BHM",IF(G1:G500000<>"",IF(M1:M500000>0,MATCH(A1:A500000&"/"&G1:G500000,A1:A5000&"/"&G1:G500000,0)))),ROW($A1:A500000)-ROW($A$1)+1),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RodneyC]

Thanks. I've had a swamped day and haven't had tome to look at this. I'll try over the weekend, but most likely on Monday. Thanks again