how to count the Distinct value met specific condition

[hamid sajjadi]

Hello.
I have two simple columns as follow :

A B
city A 130
city A 170
city B 200
city A 300

I need to distinct count the city<200 . i.e =1.
how can I do this? i've tried many ways but got no answer

 

[KRice]

If you are looking for counts for each city, then try this...you can hardwire the 200 value into the formula if desired:

Book1
	A	B	C	D	E	F
1	City	Value		City	<threshold	Counts
2	A	130		A	200	2
3	A	170		B	200	0
4	B	200
5	A	300
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=COUNTIFS($A$2:$A$5,D2,$B$2:$B$5,"<"&E2)

 

[kweaver]

It would help if you changed your profile to show what version of Excel you run because there are features in 365, for example, that could be used that aren't in earlier versions.

With 365, if I understand your issue, this might solve it:

Book1
	A	B	C	D
1	City A	130	200	2
2	City A	170
3	City B	200
4	City A	300
5	City C	600
6	City X	100
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(UNIQUE(FILTER(A1:A6,B1:B6<C1)))

 

[KRice]

Sorry...I think I misunderstood your question. What I offered before gives a count for each city specified (green cells in example below), but it sounds like you want to count the number of unique cities that have values less than 200, which is what kweaver's solution does. If you don't have 365, the solution is more difficult (see the yellow cells below):

Book1
	A	B	C	D	E	F
1	City	Value		City	<threshold	Counts
2	A	130		A	200	2
3	A	170		B	200	0
4	B	200
5	A	300
6	C	600			200	2
7	X	100
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=COUNTIFS($A$2:$A$5,D2,$B$2:$B$5,"<"&E2)
F6	F6	=SUM(--(FREQUENCY(IF($B$2:$B$7<E6,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[hamid sajjadi]

Sorry...I think I misunderstood your question. What I offered before gives a count for each city specified (green cells in example below), but it sounds like you want to count the number of unique cities that have values less than 200, which is what kweaver's solution does. If you don't have 365, the solution is more difficult (see the yellow cells below):

Book1
	A	B	C	D	E	F
1	City	Value		City	<threshold	Counts
2	A	130		A	200	2
3	A	170		B	200	0
4	B	200
5	A	300
6	C	600			200	2
7	X	100
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=COUNTIFS($A$2:$A$5,D2,$B$2:$B$5,"<"&E2)
F6	F6	=SUM(--(FREQUENCY(IF($B$2:$B$7<E6,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW($A$2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

thank you. it seems great. i have to try & expand this formula in my real senario.

 

[hamid sajjadi]

It would help if you changed your profile to show what version of Excel you run because there are features in 365, for example, that could be used that aren't in earlier versions.

With 365, if I understand your issue, this might solve it:

Book1
	A	B	C	D
1	City A	130	200	2
2	City A	170
3	City B	200
4	City A	300
5	City C	600
6	City X	100
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(UNIQUE(FILTER(A1:A6,B1:B6<C1)))

unfortunately i do not have office 365

 

[KRice]

We're happy to help. Thanks for updating your profile and the feedback...and post back if you encounter any problems adapting the solution to your scenario.