+ Reply to Thread
Results 1 to 1 of 1

Frequency of occuring values excluding duplicates

  1. #1
    Registered User
    Join Date
    01-23-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Frequency of occuring values excluding duplicates

    Hi there, thanks for looking at my thread!

    I have enclosed an example spread sheet with a small part of the table im using at the minute.

    The task is to count the frequency of the occurances of various 'Mod revision's in column C. However, if the entry is a duplicate 'Mod Number', then it is to be excluded from the count.

    I managed to find a very similar example online and it provided me with the following formula:

    {=SUM(IF(FREQUENCY(IF($B$2:$B$13<>"-",IF($C$2:$C$13="A",MATCH($B$2:$B$13,$B$2:$B$13))),ROW($B$2:$B$13)-ROW($B$2)+1),1))}

    This formula works alright in some aspects, however it brings back some confusing results some times. For simplicity i reduced the size of the table from 1300 to 21. I notice that whenever i increase the parameters in the formula from 13 to 50, i start to get '#n/a# results for a number of the 'mod revisions'. Even increasing them from 13 to 20 causes some undesirable effects, as illustrated by the tables below it. Expanding from 13 to 20 causes the 'A' result to actually decrease, meanwhile the 'B' result increases a little bit.

    Im not sure why the formula worked so well for the person i acquired it from, but it seems to go haywire for me! Any help on this matter would be greatly appreciated!

    P.S. While messing around with it I noticed another strange effect: Whenever you change Cell B11 from 'abc1' to 'abc6' it manages to increase the 'A' count but decrease the 'B' count!
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Frequency of occuring values excluding duplicates

    =SUM(IF(FREQUENCY(IF($B$2:$B$20<>"-",IF($C$2:$C$20=A23,MATCH($B$2:$B$20,$B$2:$B$20,0))),ROW($B$2:$B$20)-ROW($B$2)+1),1)) but b should =4 as its duplicated in b2 and b11
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Frequency of months occuring
    By gbug1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-04-2012, 10:03 AM
  2. [SOLVED] Linking to a column of values, excluding duplicates?
    By bauerbach in forum Excel General
    Replies: 5
    Last Post: 06-25-2012, 02:13 PM
  3. Frequency Array Excluding Zeros
    By basalganglia in forum Excel General
    Replies: 7
    Last Post: 11-20-2011, 04:28 PM
  4. Identifying duplicates and their frequency
    By helpmeplz55 in forum Excel General
    Replies: 3
    Last Post: 10-12-2011, 06:14 PM
  5. frequency counts excluding repeated values
    By climate in forum Excel General
    Replies: 1
    Last Post: 03-09-2011, 12:59 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1