Results 1 to 6 of 6

Thread: Conditional Frequency formula?

  1. #1

    Conditional Frequency formula?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    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.
    Circumference of a circle = 2πr²



    ²the circle's radius

  3. #3
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,321
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Eventually a Pivot Table ?

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    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.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Sorry - I got the wrong end of the stick, having looked at this again. I agree with Pecoflyer - use a Pivot Table.

  6. #6
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •