How to use COUNTIF and FREQUENCY in combination?

[khavusr]

I'm currently using this formula to count the frequency words used in Columns:

=SUM(IF(FREQUENCY(IF(LEN(G2:G100)>0;MATCH(G2:G100;G2:G100;0);""); IF(LEN(G2:G100)>0;MATCH(G2:G100;G2:G100;0);""))>0;1))

But, would like to include COUNTIF to only count the frequency if it also matches and additional phrase in another Column; so the pre-check should be using:

=COUNTIF(E2:E100; "Country")

How would the above play out adding the COUNTIF to frequency sum formula above; currently banging my head and end up with formula error and it's structure error but getting kind of blind on this one by now:

=COUNTIF((E2:E100; "Country"); (IF(FREQUENCY(IF(LEN(G2:G100)>0;MATCH(G2:G100G2:G100;0);""); IF(LEN(G2:G100)>0;MATCH(G2:G100;G2:G100;0);""))>0;1)))

 

[jasonb75]

The 2 don't go really go together, it's always better to explain in words (preferably with a sample using XL2BB, not screen captures or copy and paste).

Maybe this, array confirmed?

=SUMPRODUCT(IFERROR(1/COUNTIFS(G2:G100;G2:G100&"";E2:E100;E2:E100,E2:E100;"Country");0))

 

[khavusr]

Thanks for the input Jason. What I'm trying to do is to count the numbers of countries linked to a specific department. So in this below example Dep1 has 4 different countries linked (United Kingdom, Unites States, Sweden and France) and Dep2 has 1 country linked (Germany). So the Country value should only be counted once for each Department. Hope this makes it a bit more clear what I'm trying to do and guess that I over complicated with my own efforts

Country:	Department
United Kingdom	Dep1
United States	Dep1
Sweden	Dep1
Germany	Dep2
United Kingdom	Dep1
France	Dep1

 

[jasonb75]

You mean like this?

Book1
	A	B	C	D	E
1	Country:	Department
2	United Kingdom	Dep1		Dep1	4
3	United States	Dep1		Dep2	1
4	Sweden	Dep1
5	Germany	Dep2
6	United Kingdom	Dep1
7	France	Dep1
Sheet4

Cell Formulas
Range	Formula
E2:E3	E2	=SUMPRODUCT(IFERROR(1/COUNTIFS(A2:A100,A2:A100&"",B2:B100,B2:B100,B2:B100,D2),0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[khavusr]

Yes, the above did the trick for what I needed. How do I avoid having this formula to include empty cells as value? Meaning if you in the above example had an additional Row 8 where A8 would be empty and B8 says Dep1 then the B8 would increase the value in E2 to 5; so it's counting the empty cell as a valid entry. How is this avoided?

 

[jasonb75]

If they are truly empty then

=SUMPRODUCT(IFERROR(1/COUNTIFS(A2:A100,A2:A100,B2:B100,B2:B100,B2:B100,D2),0))

If they are blank (look empty but contain a formula) then I'll take another look at frequency.

 

[khavusr]

Ah, Missed the &"" and with this removed it works out fine. Ok, the SUMPRODUCT makes better sense now. Good Stuff!