Count the group that has a value on other column

[mark692]

hi guys i have this table with 2 column, Column Name and Column Fruit Group i want a formula that will count a Fruit Group that has a name on Column Name, is that possible? thanks

Book1
	A	B
1	Name	Fruit group
2	james	mango
3	Jessie	mango
4		mango
5		grapes
6	Jessica	orange
7		orange
8		orange
9	Minnie	Guava
10	gokou	Guava
11	Klilin	Guava
12		Guava
13		Banana
14		Banana
Sheet1

in my example the Fruit Group that has a names are Mango, Orange, Guava, that is 3 group, i want a formula that will give me 3.

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D
1	Name	Fruit group
2	james	mango		3
3	Jessie	mango
4		mango
5		grapes
6	Jessica	orange
7		orange
8		orange
9	Minnie	Guava
10	gokou	Guava
11	Klilin	Guava
12		Guava
13		Banana
14		Banana
Sheet3

Cell Formulas
Range	Formula
D2	D2	=SUM(--(FREQUENCY(IF(A2:A14<>"",MATCH(B2:B14,B2:B14,0)),ROW(B2:B14)-ROW(B2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CA_Punit]

Just tried a different method

Book1
	A	B	C	D	E	F
1	Name	Fruit group
2	james	mango				3
3	Jessie	mango
4		mango
5		grapes
6	Jessica	orange
7		orange
8		orange
9	Minnie	Guava
10	gokou	Guava
11	Klilin	Guava
12		Guava
13		Banana
14		Banana
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(--ISNUMBER(MATCH(ROW($A$1:$A$14),MATCH(IF(ISTEXT($B$2:$B$14)/ISTEXT($A$2:$A$14)=1,$B$2:$B$14),IF(ISTEXT($B$2:$B$14)/ISTEXT($A$2:$A$14)=1,$B$2:$B$14),0),0)))

This formula is dependent when there is only text in both the column. If there is number then it wont work considering ISText Function used

 

[mark692]

How about

+Fluff New.xlsm
	A	B	C	D
1	Name	Fruit group
2	james	mango		3
3	Jessie	mango
4		mango
5		grapes
6	Jessica	orange
7		orange
8		orange
9	Minnie	Guava
10	gokou	Guava
11	Klilin	Guava
12		Guava
13		Banana
14		Banana
Sheet3

Cell Formulas
Range	Formula
D2	D2	=SUM(--(FREQUENCY(IF(A2:A14<>"",MATCH(B2:B14,B2:B14,0)),ROW(B2:B14)-ROW(B2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

thank you for this! this works

 

[Fluff]

Glad we could help & thanks for the feedback.