Conditional Frequency formula?

DMJC

New member
Joined
Oct 8, 2013
Messages
1
Reaction score
0
Points
0
Hi all,

Does anyone have an idea how I count the number of unique reference number for each name in an xls? So for example, as attached, Bill has two unique reference numbers (need to be done on much bigger set of data or I would just count!)

Any suggestions would be much appreciated!

Dave
 

Attachments

  • Copy of Refs.xls
    52 KB · Views: 20

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
237
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
I would sort the data by Originator then by PRN, then use a formula like:
=IF(AND(B9=B8,C9=C8),0,1)
You can then use SUMIF formulas or a pivot table to create a simple summary of unique counts.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hi DMJC and welcome to the forum

You can also use the Advanced Filter that allows you to replace your full list with a list of unique entries, or you can place the output in a different location. I think its available in the older versions of Excel, but the menu structure could be different from 2010, which I use mostly.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Sorry - I got the wrong end of the stick, having looked at this again. I agree with Pecoflyer - use a Pivot Table.
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
Or maybe a formula approach, see the attach file. The formula in column E is an array formula, "CSE" to commit the formula and copy down.
 

Attachments

  • Copy of Refs (1).xls
    74.5 KB · Views: 18
Top