Counting unique values with multiple arguments

dgexcellent

New Member
Hi, I have a 900+ line data set like the below. What I need is a formula that counts the number of unique occurrences in the second column in reference to the first. This value would then be referenced into a second table using the unique values of the first.

So I'm after two formulas :
create a list of the unique values from Data1 (having this auto-sorted numerically would be fantastic but not vital)
count the number of unique values in Data2.

Dave

Data and intended Results look like :
 Data1 Data2 Results No. Of Unique Values in Data 2 f720298 3608866336 f720298 1 f722942 9897888929 f722942 1 f724251 3501430051 f724251 2 f724251 3501430051 f807531 1 f724251 9897888929 g341351 1 f807531 1867781528 g341351 1867781528

<tbody>
</tbody>

Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi Dave,

Is this what you require?...

Excel Workbook
ABCDEF
1Data1Data2*ResultsNo. Of Unique Values in Data 2*
2f7202983608866336*f7202981*
3f7229429897888929*f7229421*
4f7242513501430051*f7242512*
5f7242513501430051*f8075311*
6f7242519897888929*g3413511*
7f8075311867781528****
8g3413511867781528****
9******
Sheet3

The formulas need entering with ctrl shift enter NOT just enter, they can then be copied down as far as you require.
You will obviously have to change the cell references to suit your layout!!

Solutions found here....

Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Excel resource

I hope that helps.

Ak

 Data1 Data2 Results Distinct count f720298 3608866336 f720298 1 f722942 9897888929 f722942 1 f724251 3501430051 f724251 2 f724251 3501430051 f807531 1 f724251 9897888929 g341351 1 f807531 1867781528 g341351 1867781528

<colgroup><col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4437;" width="125"> <col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4494;" width="126"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;" width="93"> <col style="width: 102pt; mso-width-source: userset; mso-width-alt: 4835;" width="136"> <tbody>
</tbody>

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$8<>"",
IF(\$A\$2:\$A\$8=E2,MATCH("~"&\$B\$2:\$B\$8,\$B\$2:\$B\$8&"",0))),
ROW(\$B\$2:\$B\$8)-ROW(\$B\$2)+1),1))``````

If the target entries are never surrounded with specila meaning chars, the "~"& and &"" bits can be removed from the formula.

Worked perfectly, thank you very much

this is assuming that it is starts in b1 I know that you got it to work, but I thought I would put a formula on here too.
=sumproduct(1/countif(b1:b900,b1:b900)) and that will do it

whats wrong with my formula?

It's not a question of the formula under question being wrong, rather:

1) It does not anwer the OP's question, which involves a conditional distinct count.
2) It will flounder on possible blank/empty cells (although it can be made to understand such situations), hence the first links which attempt to explain how it works and how it is affected in a situation with blank/empty cells.
2) It's not very efficient (hence the link on performance).

i didnt understand his question

thank you, I didnt know that it was so bad on performance

Replies
5
Views
581
Replies
32
Views
1K
Replies
0
Views
326
Replies
0
Views
2K
Replies
5
Views
786

1,220,987
Messages
6,157,236
Members
451,407
Latest member
vdaesety

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.

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

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