+ Reply to Thread
Results 1 to 14 of 14

Count an array's frequency based on another arrays criteria,only counting duplicates once

  1. #1
    Registered User
    Join Date
    02-04-2015
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    7

    Talking Count an array's frequency based on another arrays criteria,only counting duplicates once

    Hi Guys,

    I need a formula to count the amount of dates whilst only counting the duplicates once. So the formula next to 128809 will = 4, as there are only 4 dates (ie the 07/01/2015 is counted as 1 not for 2). the formula will need to work consequtively down the spread sheet. Next to 202032 the formula should = 1 and next to 201727 the formula should equal 2

    Below is the example (Note two columns are being used, A & B):

    128809 30/10/2014
    128809 29/10/2014
    128809 05/11/2014
    128809 07/01/2015
    128809 07/01/2015
    202032 05/03/2015
    201727 28/03/2015
    201727 29/03/2015
    201727 29/03/2015

    I have been working with IF, CountIF, Sum, Match and Frequency, but i just cant get it right.

    any help is appreciated.

    THanks guys :D

  2. #2
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    E1=128809

    F1

    Please Login or Register  to view this content.
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    @Ali Kırksekiz

    A good construction, although I don't understand the use of INDEX here. Such a syntax would suggest that you are attempting to avoid array-entry, though of course CSE is still necessary, even with the additional INDEX.

    Simply:

    =COUNT(1/FREQUENCY(IF($A$1:$A$10=E1,IF($B$1:$B$10<>"",MATCH($B$1:$B$10,$B$1:$B$10,0))),ROW($B$1:$B$10)-ROW($B$1)+1))

    is sufficient.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    For dates (or numbers) you can also use this approach:

    =SUM(IF(FREQUENCY(IF(A$1:A$10=E1,B$1:B$10),B$1,B$10),1))
    Audere est facere

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    @daddylonglegs

    Yes - I didn't even check what the datatype in question was!

    Cheers

  6. #6
    Forum Contributor
    Join Date
    01-28-2014
    Location
    Turkey
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    Hi XOR LX

    I do agree with you about the fact. It is not necessary INDEX for this formula. Only, ROW formula is enough.

    Thank you for the information.

  7. #7
    Registered User
    Join Date
    02-04-2015
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    7

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    Hi Guys,

    Thank you for your replies :D, Unfortunately these formulas are only returning a value of zero. Also I do not understand the theory of using E1 (E1=128809), as E1 will only represent one number. I have thousands of numbers in Column A...

    The data is both general format (Column A) and date format (Column B). For simplicity, lets say the data starts in A1 and B1 and i want to put this formula in C1. In C1 I need the formula to tell me the count of dates (Column B), correlating to the numbers in Column A.

    Example Data (My data is thousands of lines long, so this is just a small sample):

    128809 30/10/2014
    128809 29/10/2014
    128809 05/11/2014
    128809 07/01/2015
    128809 07/01/2015
    202032 05/03/2015
    201727 28/03/2015
    201727 29/03/2015
    201727 29/03/2015

    In this Example C1-C5 should be 4, C6 should be 1 and C7-C9 should be 2. Remebering that we want to count duplicates only once!

    Hope you guys can help out some more. We are on track!!
    Last edited by areichst; 04-08-2015 at 06:28 PM.

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    If you want to copy down like that you can use this version in C1

    =SUM(IF(FREQUENCY(IF(A$1:A$1000=A1,B$1:B$1000),B$1:B$1000),1))

    [adjust ranges to cover all your data but don't use whole columns]

    confirmed with CTRL+SHIFT+ENTER and copied down

    .......but if you do this over thousands of rows then calculation times will be very slow.

    If you have the data grouped like that you could get the result just on the top row of each group like this:

    First insert a header row if you don't have one then in C2 you can use this formula:

    =IF(A2=A1,"",SUM(IF(FREQUENCY(IF(A2:A$1000=A2,B2:B$1000),B2:B$1000),1)))

    again confirmed with CTRL+SHIFT+ENTER and copied down
    Last edited by daddylonglegs; 04-08-2015 at 06:27 PM.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,796

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    Quote Originally Posted by daddylonglegs View Post
    For dates (or numbers) you can also use this approach:

    =SUM(IF(FREQUENCY(IF(A$1:A$10=E1,B$1:B$10),B$1,B$10),1))
    DDL, I get "too many arguments" with that?

    It highlights B$10 (and B$1000)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    Quote Originally Posted by FDibbins View Post
    DDL, I get "too many arguments" with that?
    Thanks, Ford - I had a comma where it should be a semi-colon, should be

    =SUM(IF(FREQUENCY(IF(A$1:A$10=E1,B$1:B$10),B$1:B$10),1))

    Now corrected in my last post

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,796

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    duh, I should have seen that, as well. (Im not as familiar with Freq as I should be)

  12. #12
    Registered User
    Join Date
    02-04-2015
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    7

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    [QUOTE=daddylonglegs;4040550]If you want to copy down like that you can use this version in C1

    =SUM(IF(FREQUENCY(IF(A$1:A$1000=A1,B$1:B$1000),B$1:B$1000),1))

    Hi Daddylonglegs. I tried this formula however it is returning different counts for the same numbers in Column A... Attached is a screen shot.

    The formula in Column D needs to represent the date count (COunting duplicates only once in Column L). For each 207606 in column B i want to count all the dates in column L that are also linked to a 207606 in column B (only counting the duplicates once.

    So as you can see the first two lines in Column D output 17 but then the third onwards begin to give out different numbers???
    Last edited by areichst; 04-08-2015 at 07:45 PM.

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    Sorry, I have no idea what causes that - I believe the suggested formula should do what you want, and your amended version seems to be OK - is it the case that there are only 17 instances of 207606 in column B, so the "different date count" result shouldn't be higher than 17? How are you getting the count in column C, is that a COUNTIF formula?

    Can you post a small sample or copy of the real data (e.g. 1000 rows, sanitised if necessary)?

  14. #14
    Registered User
    Join Date
    02-04-2015
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    7

    Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

    Hi Daddylong legs,

    I have just returned from a holiday with the Family. Sorry for the late reply!!

    Since being back in the office I have tried your formula again and it seems to be working perfectly fine now?? Not sure what I was doing wrong, possibly a simple error.

    Thank you for all your help!

+ 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] Count duplicates in column based on criteria
    By roxylai in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-23-2013, 07:32 PM
  2. [SOLVED] count with multiple criteria without duplicates based on a different column
    By antagonanin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2013, 09:46 AM
  3. [SOLVED] Count without duplicates based on multiple criteria from different cells
    By perryadam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2013, 12:54 PM
  4. Replies: 4
    Last Post: 04-16-2010, 10:09 AM
  5. Counting Frequency of duplicates for each unique value and corresponding data
    By fredman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2009, 12:55 AM

Tags for this Thread

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