Count If Distinct Values based on Criteria

[Tashat]

Hi All

Please can you help me with a formula? I am trying to count the number of distinct values based on a criteria. In this case, I want to count how many pieces of fruit Jennifer had, but I don't want to count the second fruit if it was repeated. e.g. Jennifer had two apples and two oranges, but I only want to count the first of each of these. So she had overall, 4 different pieces of fruit. Thanks in advance as always. Natasha

Book3
	A	B	C	D	E
1	Person	Fruit
2	Jennifer	Apple		Jennifer	4
3	Adam	Plum
4	Jennifer	Apple
5	Adam	Apple
6	Geoff	Banana
7	Jennifer	Orange
8	Barney	Banana
9	Jennifer	Plum
10	Amy	Kiwi
11	Sam	Cherry
12	Jack	Kiwi
13	Natasha	Plum
14	Al	Plum
15	Jennifer	Orange
16	Al	Banana
17	Jennifer	Banana
Sheet1

 

[hrayani]

=SUM(IF(FREQUENCY(IF(A$2:A$17=D2,MATCH(B$2:B$17,B$2:B$17,0)),ROW(B$2:B$17)-ROW(B$2)+1),1))

Array formula CTL+SHT+ENT

 

[Peter_SSs]

With 365 you can try this.

21 08 12.xlsm
	A	B	C	D	E
1	Person	Fruit
2	Jennifer	Apple		Jennifer	4
3	Adam	Plum
4	Jennifer	Apple
5	Adam	Apple
6	Geoff	Banana
7	Jennifer	Orange
8	Barney	Banana
9	Jennifer	Plum
10	Amy	Kiwi
11	Sam	Cherry
12	Jack	Kiwi
13	Natasha	Plum
14	Al	Plum
15	Jennifer	Orange
16	Al	Banana
17	Jennifer	Banana
Count

Cell Formulas
Range	Formula
E2	E2	=ROWS(UNIQUE(FILTER(B2:B17,A2:A17=D2)))

 

[Tashat]

=SUM(IF(FREQUENCY(IF(A$2:A$17=D2,MATCH(B$2:B$17,B$2:B$17,0)),ROW(B$2:B$17)-ROW(B$2)+1),1))

Array formula CTL+SHT+ENT

Thank you so much

 

[Tashat]

With 365 you can try this.

21 08 12.xlsm
	A	B	C	D	E
1	Person	Fruit
2	Jennifer	Apple		Jennifer	4
3	Adam	Plum
4	Jennifer	Apple
5	Adam	Apple
6	Geoff	Banana
7	Jennifer	Orange
8	Barney	Banana
9	Jennifer	Plum
10	Amy	Kiwi
11	Sam	Cherry
12	Jack	Kiwi
13	Natasha	Plum
14	Al	Plum
15	Jennifer	Orange
16	Al	Banana
17	Jennifer	Banana
Count

Cell Formulas
Range	Formula
E2	E2	=ROWS(UNIQUE(FILTER(B2:B17,A2:A17=D2)))

Hi Peter_SSs - thank you so much. I've used this formula and it works perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[hrayani]

Thank you so much

you are welcome