Counting unique records with multiple criteria

[gigimarga]

Hi All,

I have the following dataset and I want the final answer to be a number of unique counts of "Record#", based on criteria 1=Variable1,"AAAA" and criteria 2=Variable2,"123". The two criteria are set as lists in a separate worksheet, so when the user selects variable 1 and variable 2, the result (formula) returns a value in a separate summary table. In the example here - based on the stipulated criteria (criteria 1=Variable1,"AAAA" and criteria 2=Variable2,"123") the value would be 2. Thanks in advance.

Record#	Variable1	Variable2
1	AAAA	123
1	AAAB	123
1	AAAA	123
2	AAAA	234
2	AAAC	234
3	AAAA	123
4	AAAA	456

 

[Eric W]

Using Peter's layout, try:

Book2
	A
1	Unique Count
2	2
Sheet3

Cell Formulas
Range	Formula
A2	A2	=SUM(SIGN(FREQUENCY(IF((Sheet1!B2:B8=Sheet2!A2)*(Sheet1!C2:C8=Sheet2!B2),MATCH(Sheet1!A2:A8,Sheet1!A2:A8,0)),ROW(Sheet1!A2:A8)-ROW(Sheet1!A2)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Hi Again,

Can't use FILTER in my Excel 2016, unfortunately.

Now you know why I made post #3

.. but thanks for now updating your details.

 

[Peter_SSs]

Another option to consider.

gigimarga.xlsm
	A
1	Unique Count
2	2
Sheet3

Cell Formulas
Range	Formula
A2	A2	=COUNT(1/(MATCH(Sheet1!A2:A8&"|"&Sheet2!A2&"|"&Sheet2!B2,Sheet1!A2:A8&"|"&Sheet1!B2:B8&"|"&Sheet1!C2:C8,0)=ROW(Sheet1!A2:A8)-ROW(Sheet1!A2)+1))
Press CTRL+SHIFT+ENTER to enter array formulas.