Distinct count of values in a column if two other cells match

Question

Struggling a bit with array formula's to count distinct values in one column, in the rows where two other cells match. Sorry if I can't explain any better. Best is to show you the formula I created and provide some sample data:

Sheet 1

Column A      Column B  
Period 101    Code X
Period 309    Code Y
Period 101    Code Y
Period 101    Code Z
Period 404    Code Y
Period 101    Code X
Period 101    Code X
Period 404    Code X
Period 404    Code Z

Sheet 2

Column A      Column B (where the formula should be)
Code X        2
Code Y        3
Code Z        2

Basically I want to count the distinct values in Sheet 1 column A, only where the value in Sheet 1 column B matches the value in sheet 2 column A. I have provided the expected outcome for the three code values.

I have tried with the following formula, but I am unable to count distinct values in another column where the two cells match:

{=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0))} 

Please ignore the rows and columns used in the formula, also the values in Column A and B on sheet 1 both occur multiple times, but the values in column one on sheet 2 only occur once.

I am curious how someone would solve this one. Thank you in advance.

Answer 1

Your formula doesn't match your sample data but let's assume the below:

Formula in H5:

=SUM(--(FREQUENCY(IF(C$5:C$13=G5,MATCH(B$5:B$13,B$5:B$13,0)),ROW(B$5:B$13)-ROW(B$5)+1)>0))

Entered as array through CtrlShiftEnter and drag down

Notice the semi-absolute cell references (you used relative ones) + how my ranges are larger than yours (you looked from C5:C11 only)

Answer 2

Caveat: This answer is unlikely to be useful to the OP, as these techniques are as yet only available to Excel Insiders

But once these new features are available to the main stream they will be a game changer.

This uses the new Dynamic Array feature coming to Excel soon.

To create the list of unique values from Column B, place this formula in a single cell. Excel will "Spill" into as many rows as needed to return the unique list of values from Column B. For example, I have used cell E2

=UNIQUE(FILTER($B:$B,$B:$B<>""))

Now, place this formula in a the next adjacent cell, I've used F2

=COUNTA(UNIQUE(FILTER($A:$A,$B:$B=$E$2)))

Again, you only need to put this formula in one cell, no need to copy down. Excel will "Spill" the result into as many cells as needed, to match column E.