« ListView
Power Formulas for Unique Data »

Formulas for Unique Data

26th December 2006, 08:29 pm by Dick Kusleika

Created by David Hager

This array formula returns the number of unique items in a worksheet range.

=SUM(1/COUNTIF(Rng,Rng))

However, if Rng contains blank cells, this formula returns an error. In this case, use this modified version of the formula.

=SUM(COUNTIF(Rng,Rng)/IF(NOT(COUNTIF(Rng,Rng)),1,COUNTIF(Rng,Rng))^2)

This array formula returns the Nth largest unique value in a column range.

=LARGE(IF(MATCH(Rng,Rng,0)=ROW(Rng)-MIN(ROW(Rng))+1,Rng,""),N)

To apply data validation to a column which allows only unique items to be entered, highlight that column and select Data, Validation from the menu. Choose the custom option and enter the following formula (for column A):

=COUNTIF($A$1:A1,A1)=1

Created by Laurent Longre

This formula counts the number of unique items a column range, only if the cells in the lookup range contain the specified string.

=SUM(N(FREQUENCY(IF(lookupRange="specifStr",MATCH(colRange,colRange,0)),MATCH(colRange,colRange,0))>0))

Editor’s Note:
rng = A1:A7
colRange = A1:A7
lookupRange = B1:B7

Don’t forget to enter those array formula with Control+Shift+Enter, not just Enter.

Category: Excel Experts E-Letter  |  Comment (RSS)  |  Trackback

10 Comments

  1. Simon Herbert:

    Similar to David’s first formula, but I normally use the following to count unique records in a set range:

    {=SUM(COUNTIF(RANGE,RANGE)/(COUNTIF(RANGE,RANGE)^2))}

    28 December 2006, 4:17 am

  2. JuTsung Pi:

    I am new to array programming. Can anyone kindly explain how the following works

    I try to figure out myself. However, I always get 1 for ROW(RNG) instead of an array
    Why we need MIN(ROW(RNG))+1? I thought MIN(ROW(RNG)) will always be 1

    This array formula returns the Nth largest unique value in a column range.
    =LARGE(IF(MATCH(Rng,Rng,0)=ROW(Rng)-MIN(ROW(Rng))+1,Rng,”"),N)

    29 December 2006, 10:52 am

  3. Tennor:

    The formula
    =COUNTIF($A$1:A1,A1)=1
    works by entering data. If you copy the data from another cell Control+C and paste it Control+V to the Column A (subject to data validation), it will be pasted and data validation will be bypassed!
    Question: How to implement proper data validation? Ensuring that either way Data Entering and/or Copy-Pasting will be validated.
    Thanks,
    Tennor

    2 January 2007, 4:22 pm

  4. Matt Cook:

    In the last array formula by Laurent Longre it is worth noting that this formula will only work when there are no blank cells in colrange.

    One way around this if you have blank cells at the end of the range is to use ozgrids expanding ranges (http://www.ozgrid.com/Excel/DynamicRanges.htm)

    Does anyone know how to make it work if the blank cells are in the middle of the range?

    Thanks,

    Matt

    29 January 2007, 8:46 am

  5. Natasha kuipers:

    I am trying to validate a column so that it will send an error alert when 07……..telephone numbers and 09…….. telephone numbers are entered into the column. I am a bit confused about the custom formula to validate this cell. can you help

    5 March 2007, 6:00 am

  6. Dick Kusleika:

    Natasha: Are 07 and 09 country codes? Can you give some examples of phone numbers that should give the alert?

    5 March 2007, 6:53 pm

  7. Brian Price:

    Natasha,

    I assume your trying to catch mobile phone numbers (beginning 07…) and premium rate lines (beginning 09…).

    Make sure your phone number column is formatted as text.

    Apply the following custom formula in data validation

    =AND(LEFT(cell_ref,2)”07″,LEFT(cell_ref,2)”09″)

    and add the appropriate warning in your Error Alert.

    Brian

    6 March 2007, 6:46 am

  8. Brian Price:

    Oops! There should be “not equal” signs after the last bracket for each LEFT function. Soz.

    6 March 2007, 6:48 am

  9. Paul McGrath:

    Data Validation:
    Is it possible to apply the =COUNTIF($H$1:H1,H1)=1 across many worksheets? I have split an asset register across many worksheets and I would like it to check that the serial number does not exist in any worksheet. Is that possible?
    Thanks
    Paul

    16 April 2008, 8:17 am

  10. Hima:

    Hi

    I need help in counting the unqiue records,

    Column
    Device Type Device Model
    MFP Canon NP 6050
    Printer HP Color LaserJet 2500N
    Printer HP Color LaserJet 4600n
    Printer HP LaserJet 1010
    Printer HP LaserJet 4000
    Printer HP LaserJet 4050
    Printer HP LaserJet 4100N
    Printer HP LaserJet 4200N
    Printer HP LaserJet 4200N
    Printer HP LaserJet 4200N
    Printer HP LaserJet 4200N
    Fax Panasonic AnyModel
    MFP Canon NP 6050
    Printer HP Color LaserJet 2500N
    Printer HP Color LaserJet 2550N
    Printer HP Color LaserJet 3700N

    this is my data, now i need to if count unique record in cloumn device model where device type is printer.can some one please help me,

    17 August 2008, 8:56 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment