Conditional Frequency formula?

D

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
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.
 
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.
 
Sorry - I got the wrong end of the stick, having looked at this again. I agree with Pecoflyer - use a Pivot Table.
 
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
You must log in or register to reply here.
Back
Top