+ Reply to Thread
Results 1 to 8 of 8

Count formula without counting duplicates

  1. 02-12-2016, 07:58 PM #1
    jason4444
    jason4444 is offline
    Registered User
    Join Date
    02-12-2016
    Location
    paramus, nj
    MS-Off Ver
    excel 2015
    Posts
    16

    Count formula without counting duplicates

    Hi all,

    The attached shows the process that I currently use in order to count all the unique values in column B without counting duplicates. The problem with this formula is that it requires me to sort column B and some of the large files keep freezing when I sort. I am hoping there is a formula that will count the values without counting duplicates and that doesn't require me to sort column B. My goal is to make a summary by year and please note that duplicates have the same year.

    Thank you,
    Jason
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-12-2016, 08:07 PM #2
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count formula without counting duplicates

    hi and welcome to the forum
    try below for total unique count
    =SUMPRODUCT(--(FREQUENCY(B2:B17,B2:B17)>0))

    in F10 Type 2011, F11 type 2012 , f12 type 2013 then In G10 copy paste below then hold control and shift together and then hit enter to make it array formula
    =SUM(--(FREQUENCY(IF($A$2:$A$17=$F10,$B$2:$B$17),IF($A$2:$A$17=$F10,$B$2:$B$17))>0))
    and drag down
    Last edited by hemesh; 02-12-2016 at 08:11 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh
    Reply With QuoteRegister To Reply
  3. 02-12-2016, 08:44 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count formula without counting duplicates

    Quote Originally Posted by hemesh View Post
    =SUMPRODUCT(--(FREQUENCY(B2:B17,B2:B17)>0))
    SUM will do:

    =SUM(--(FREQUENCY(B2:B17,B2:B17)>0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 02-13-2016, 06:21 AM #4
    Czeslaw
    Czeslaw is offline
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,292

    Re: Count formula without counting duplicates

    I suggest this option.
    See the example.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 02-13-2016, 06:24 AM #5
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count formula without counting duplicates

    Quote Originally Posted by Tony Valko View Post
    SUM will do:

    =SUM(--(FREQUENCY(B2:B17,B2:B17)>0))
    Indeed! Powerful function FREQUENCY!

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  6. 02-13-2016, 08:27 AM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count formula without counting duplicates

    Quote Originally Posted by Czeslaw View Post
    See the example.
    https://www.excelforum.com/showthread.php?t=1040120 ----------
    Reply With QuoteRegister To Reply
  7. 02-14-2016, 09:35 PM #7
    jason4444
    jason4444 is offline
    Registered User
    Join Date
    02-12-2016
    Location
    paramus, nj
    MS-Off Ver
    excel 2015
    Posts
    16

    Re: Count formula without counting duplicates

    Thank you all for your responses. I'd like to learn more about the solutions that you have all provided:

    How does a Frequency formula work? What makes a formula an array formula? In this formula, =COUNTIFS(A$2:A$17,F2,D$2:D$17,1) what does the "1" do at the end of the formula?
    Reply With QuoteRegister To Reply
  8. 02-14-2016, 10:07 PM #8
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Count formula without counting duplicates

    Quote Originally Posted by Tony Valko View Post
    SUM will do:

    =SUM(--(FREQUENCY(B2:B17,B2:B17)>0))
    another solution:
    =SUMPRODUCT(1/COUNTIF(B2:B17,B2:B17))
    Quang PT
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need formula to Count the Date without duplicates
    By mackypogi in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-29-2015, 02:01 PM
  2. Count an array's frequency based on another arrays criteria,only counting duplicates once
    By areichst in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-19-2015, 11:51 PM
  3. [SOLVED] Count If without counting duplicates?
    By excelnoobz93 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-05-2013, 03:13 PM
  4. Count Total of items in a column w/o counting duplicates or blanks
    By Clairebear4 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2013, 10:56 PM
  5. [SOLVED] How to count without counting duplicates?
    By excelbro in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-08-2012, 08:53 PM
  6. Using a Formula to Count Duplicates in two Columns
    By JungleJme in forum Excel General
    Replies: 2
    Last Post: 08-25-2010, 07:01 AM
  7. counting duplicates within array formula?
    By etmac in forum Excel General
    Replies: 5
    Last Post: 09-05-2006, 04:45 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1