Count unique text value based on another column, with long cell range

[nostradamus]

Hello dear all,

(I am using this table just for reference, so the Formula will be typed in Cell D2)
I have a complex question - I need a formula for:

1) Calculate Unique occurrences from Col.C that belong to "Texas" from Col.A. So, here the answer will be 8.
2) If I do not have any data entered in Columns A,B,C, Cell D2 should remain blank with nothing in it.
3) I need the formula to be able to cover range from Col.C2:Col.C1000. Caveat here is that sometimes data entered may be from 50 to 200 cells, and not the complete 1000 cell range in formula. So I want this formula to be able to cover the range of blank cells too if possible.

I am trying to make my requests as clear as possible, please ask if you need any more clarifications, or feel free to make suggestions to improve my solution.
Thanks!!!

Excel Workbook
	A	B	C	D	E	F	G	H
1	State	County	Area	Pop.	StateFreq	CountyFreq	AreaFreq	expectedAreaFreq
2	Texas	Travis	Bee Cave	55	11	7	1	1
3	Texas	Travis	Cele	23			1	1
4	Texas	Travis	Littig	33			1	1
5	Texas	Travis	Elroy	8			4	1
6	Texas	Travis	Elroy	15
7	Texas	Travis	Elroy	23
8	Texas	Travis	Elroy	34
9	Texas	Gonzales	Smiley	22		2	1	1
10	Texas	Gonzales	Pilgrim	31			1	1
11	Texas	Bell	Holland	14		1	1	1
12	Texas	Bexar	San Antonio	5124		1	1	1
13	Colorado	Douglas	Lone Tree	11	8	6	1	1
14	Colorado	Douglas	Castle Pines	12			3	1
15	Colorado	Douglas	Castle Pines	31
16	Colorado	Douglas	Castle Pines	14
17	Colorado	Douglas	Pinery	26			1	1
18	Colorado	Douglas	Larkspur	56			1	1
19	Colorado	Denver	Glendale	25		2	1	1
20	Colorado	Denver	Bow Mar	224			1	1
21	Florida	Sarasota	Sarasota Springs	456	9	5	1	1
22	Florida	Sarasota	Venice	600			1	1
23	Florida	Sarasota	Plantation	221			1	1
24	Florida	Sarasota	North Port	75			1	1
25	Florida	Sarasota	Meadows	23			1	1
26	Florida	Palm Beach	Wellington	32		4	4	1
27	Florida	Palm Beach	Wellington	45
28	Florida	Palm Beach	Wellington	46
29	Florida	Palm Beach	Wellington	12
Sheet1

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[nostradamus]

It is Office 365

 

[Peter_SSs]

It is Office 365

As requested by Fluff, please put this in your account details (click your user name at the top right of the forum. Don’t forget to scroll down & ‘Save’) so helpers always know.

You said the formula will go into D2 but column D already has data in it. Can you clarify?

Better still, can you post your sample data again (with XL2BB) but with the expected results manually entered in?

 

[nostradamus]

Thanks for profile update suggestion. So here is the data table cleaned out

test.xlsx
	A	B	C	D	E	F	G
1	State	County	Area	Availability		Avail.-Area Unique Count
2	Texas	Travis	Bee Cave	Yes		8
3	Texas	Travis	Cele	No
4	Texas	Travis	Littig	No
5	Texas	Travis	Elroy	Yes		Texas-Area Unique Count
6	Texas	Travis	Elroy	Yes		7
7	Texas	Travis	Elroy	Yes
8	Texas	Travis	Elroy	Yes
9	Texas	Gonzales	Smiley	No
10	Texas	Gonzales	Smiley	No
11	Texas	Bell	Holland	No
12	Texas	Bexar	San Antonio	No
13	Colorado	Douglas	Lone Tree	No
14	Colorado	Douglas	Castle Pines	Yes
15	Colorado	Douglas	Castle Pines	Yes
16	Colorado	Douglas	Castle Pines	Yes
17	Colorado	Douglas	Pinery	No
18	Colorado	Douglas	Larkspur	Yes
19	Colorado	Denver	Glendale	Yes
20	Colorado	Denver	Bow Mar	No
21	Florida	Sarasota	Sarasota Springs	No
22	Florida	Sarasota	Venice	Yes
23	Florida	Sarasota	Plantation	No
24	Florida	Sarasota	North Port	Yes
25	Florida	Sarasota	Meadows	Yes
26	Florida	Palm Beach	Wellington	No
27	Florida	Palm Beach	Wellington	No
28	Florida	Palm Beach	Wellington	No
29	Florida	Palm Beach	Wellington	No
30
Sheet4

Need Two Formulas for:

1) Col F6 has unique counts of Area (from Col C) that occur in "Texas" (from Col A) - (expected answer in F6)

2) Col F2 has counts of "Yes" (from Col D) connected to unique occurrence in Area (Col C) - so this is a multiple criteria formula - (expected answer in F2)

 

[Fluff]

Thanks for that, how about

+Fluff v2.xlsm
	A	B	C	D	E	F
1	State	County	Area	Availability		Avail.-Area Unique Count
2	Texas	Travis	Bee Cave	Yes		8
3	Texas	Travis	Cele	No
4	Texas	Travis	Littig	No
5	Texas	Travis	Elroy	Yes		Texas
6	Texas	Travis	Elroy	Yes		7
7	Texas	Travis	Elroy	Yes
8	Texas	Travis	Elroy	Yes
9	Texas	Gonzales	Smiley	No
10	Texas	Gonzales	Smiley	No
11	Texas	Bell	Holland	No
12	Texas	Bexar	San Antonio	No
13	Colorado	Douglas	Lone Tree	No
14	Colorado	Douglas	Castle Pines	Yes
15	Colorado	Douglas	Castle Pines	Yes
16	Colorado	Douglas	Castle Pines	Yes
17	Colorado	Douglas	Pinery	No
18	Colorado	Douglas	Larkspur	Yes
19	Colorado	Denver	Glendale	Yes
20	Colorado	Denver	Bow Mar	No
21	Florida	Sarasota	Sarasota Springs	No
22	Florida	Sarasota	Venice	Yes
23	Florida	Sarasota	Plantation	No
24	Florida	Sarasota	North Port	Yes
25	Florida	Sarasota	Meadows	Yes
26	Florida	Palm Beach	Wellington	No
27	Florida	Palm Beach	Wellington	No
28	Florida	Palm Beach	Wellington	No
29	Florida	Palm Beach	Wellington	No
30
Work

Cell Formulas
Range	Formula
F2	F2	=ROWS(UNIQUE(FILTER(C2:C29,D2:D29="Yes")))
F6	F6	=ROWS(UNIQUE(FILTER(C2:C29,A2:A29=F5)))

 

[nostradamus]

Thanks, both your formulas worked great!!!

 

[Fluff]

Glad we could help & thanks for the feedback.