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:

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* *
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,121
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
768
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,121

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,121
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,078,394
Messages
5,339,938
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top