Filter one range and count the associated unique values in another range.

[blastoman334]

First off, I'm a sort of a beginner at excel. I'm trying to extract data from a spread sheet of farmers and the fiscal quarter during which we purchased moringa seeds from them. However there are duplicates of the farmers due to multiple purchases from the same farmer. I'm trying to determine the number of unique farmers we worked with by fiscal quarter, without duplicates. My first approach was a countifs formula and then make the criteria the specific fiscal quarter and aunique formula, but that doesn't work because the second part of the countifs doesn't have two criteria or at least I can't figure out the second criterion. Does anyone have any suggestions?

[José Augusto]

Send a sample file.

[Tony Valko]

I'm trying to determine the number of unique farmers we worked with by fiscal quarter

Try this...

Data Range

	A	B	C	D	E
1	Name	Quarter	------	Quarter	Unique Count
2	Farmer 2	Q1		Q1	3
3	Farmer 2	Q1		Q2	3
4	Farmer 3	Q1		Q3	3
5	Farmer 5	Q1		Q4	1
6	Farmer 2	Q2
7	Farmer 3	Q2
8	Farmer 8	Q2
9	Farmer 8	Q2
10	Farmer 10	Q3
11	Farmer 10	Q3
12	Farmer 2	Q3
13	Farmer 3	Q3
14	Farmer 10	Q4
15	Farmer 10	Q4

This array formula** entered in E2 and copied down:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I have the data sorted just to make it easier to see what the results should be.