Counting unique records with multiple criteria

gigimarga

New Member
Joined
Oct 25, 2010
Messages
18
Office Version
  1. 2016
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#Variable1Variable2
1AAAA123
1AAAB123
1AAAA123
2AAAA234
2AAAC234
3AAAA123
4AAAA456
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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’)
 
Upvote 0
With Power Query

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
ABCDEFG
1Record#Variable1Variable2Variable1Variable2Count
21AAAA123AAAA1233
31AAAB123AAAB1231
41AAAA123AAAA2341
52AAAA234AAAC2341
62AAAC234AAAA4561
73AAAA123
84AAAA456
Sheet1
 
Upvote 0
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.
 
Upvote 0
gigimarga.xlsm
ABC
1Record#Variable1Variable2
21AAAA123
31AAAB123
41AAAA123
52AAAA234
62AAAC234
73AAAA123
84AAAA456
Sheet1


gigimarga.xlsm
AB
1Variable1Variable2
2AAAA123
Sheet2


gigimarga.xlsm
A
1Unique Count
22
Sheet3
Cell Formulas
RangeFormula
A2A2=COUNT(UNIQUE(FILTER(Sheet1!A2:A8,(Sheet1!B2:B8=Sheet2!A2)*(Sheet1!C2:C8=Sheet2!B2),"")))
 
Upvote 0
Using Peter's layout this is my formula reworked:

=COUNTIFS(Sheet1!B:B,Sheet2!A2,Sheet1!C:C,Sheet2!B2)
 
Upvote 0
gigimarga.xlsm
ABC
1Record#Variable1Variable2
21AAAA123
31AAAB123
41AAAA123
52AAAA234
62AAAC234
73AAAA123
84AAAA456
Sheet1


gigimarga.xlsm
AB
1Variable1Variable2
2AAAA123
Sheet2


gigimarga.xlsm
A
1Unique Count
22
Sheet3
Cell Formulas
RangeFormula
A2A2=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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top