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

 

[Trebor76]

Hi gigimarga,

The following is based on the summary table being in Sheet2 and the data is across columns A to C (change to suit):

=COUNTIFS(Sheet2!A:A,1,Sheet2!B:B,"AAAA",Sheet2!C:C,123)

Regards,

Robert

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Record#"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Variable1", "Variable2"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book1
	A	B	C	D	E	F	G
1	Record#	Variable1	Variable2		Variable1	Variable2	Count
2	1	AAAA	123		AAAA	123	3
3	1	AAAB	123		AAAB	123	1
4	1	AAAA	123		AAAA	234	1
5	2	AAAA	234		AAAC	234	1
6	2	AAAC	234		AAAA	456	1
7	3	AAAA	123
8	4	AAAA	456
Sheet1

 

[gigimarga]

Hi gigimarga,

The following is based on the summary table being in Sheet2 and the data is across columns A to C (change to suit):

=COUNTIFS(Sheet2!A:A,1,Sheet2!B:B,"AAAA",Sheet2!C:C,123)

Regards,

Robert

Unfortunately it doesn't work.

I'll rephrase my setup: the dataset (table) is in Worksheet1, the criteria (2 cells) are in Worksheet2, and the summary (a single cell) is in Worksheet3.

 

[Peter_SSs]

gigimarga.xlsm
	A	B	C
1	Record#	Variable1	Variable2
2	1	AAAA	123
3	1	AAAB	123
4	1	AAAA	123
5	2	AAAA	234
6	2	AAAC	234
7	3	AAAA	123
8	4	AAAA	456
Sheet1

gigimarga.xlsm
	A	B
1	Variable1	Variable2
2	AAAA	123
Sheet2

gigimarga.xlsm
	A
1	Unique Count
2	2
Sheet3

Cell Formulas
Range	Formula
A2	A2	=COUNT(UNIQUE(FILTER(Sheet1!A2:A8,(Sheet1!B2:B8=Sheet2!A2)*(Sheet1!C2:C8=Sheet2!B2),"")))

 

[Trebor76]

Using Peter's layout this is my formula reworked:

=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!C:C,Sheet2!B2)

 

[Peter_SSs]

Using Peter's layout this is my formula reworked:

=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!C:C,Sheet2!B2)

Hi Robert
I'm thinking that you have missed part of the requirement

want the final answer to be a number of unique counts ..... In the example here - ..... the value would be 2.

 

[Trebor76]

Hi Robert I'm thinking that you have missed part of the requirement

In the words of that famous yellow guy - D'OH!

 

[gigimarga]

gigimarga.xlsm
	A	B	C
1	Record#	Variable1	Variable2
2	1	AAAA	123
3	1	AAAB	123
4	1	AAAA	123
5	2	AAAA	234
6	2	AAAC	234
7	3	AAAA	123
8	4	AAAA	456
Sheet1

gigimarga.xlsm
	A	B
1	Variable1	Variable2
2	AAAA	123
Sheet2

gigimarga.xlsm
	A
1	Unique Count
2	2
Sheet3

Cell Formulas
Range	Formula
A2	A2	=COUNT(UNIQUE(FILTER(Sheet1!A2:A8,(Sheet1!B2:B8=Sheet2!A2)*(Sheet1!C2:C8=Sheet2!B2),"")))

Hi Again,

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