Counting unique values with multiple arguments

dgexcellent

New Member
Joined
Nov 5, 2012
Messages
20
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.

Thanks greatly in advance,

Dave :)

Data and intended Results look like :
Data1Data2ResultsNo. Of Unique Values in Data 2
f7202983608866336f7202981
f7229429897888929f7229421
f7242513501430051f7242512
f7242513501430051f8075311
f7242519897888929g341351 1
f8075311867781528
g3413511867781528

<tbody>
</tbody>
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Data1Data2 ResultsDistinct count
f7202983608866336 f7202981
f7229429897888929 f7229421
f7242513501430051 f7242512
f7242513501430051 f8075311
f7242519897888929 g3413511
f8075311867781528
g3413511867781528

<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.
 

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769

ADVERTISEMENT

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 :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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).
 

Forum statistics

Threads
1,141,581
Messages
5,707,202
Members
421,498
Latest member
matinebi

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
Top