Results 1 to 6 of 6

Thread: Count Total Values in a Column with Including Duplicates or Blank Cells

  1. #1
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    55
    Articles
    0
    Excel Version
    2016

    Count Total Values in a Column with Including Duplicates or Blank Cells



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

    May I please get some help with a formula? I got it from a web site, and tried tweaking it, but I'm doing something wrong. So, I'm here asking for assistance.

    I want a cell in my spreadsheet to display the total number of "Artists" but without duplicates or empty/blank cells. I found a web page that has a formula that supposedly does this.
    https://www.extendoffice.com/documents/excel/2434-excel-count-exclude-duplicates.html#a1

    The formula that I'm looking at, on that web page, is this:

    =SUM(IF(FREQUENCY(MATCH(A2:A7,A2:A7,0),ROW(A2:A7)-ROW(A2)+1)=1,1))

    Please understand that I'm not an Excel expert, and am a novice with formulas. But I did notice that this formula only goes up to row A7. (Unless I am not understanding this formula--which is highly possible.)
    Anyway…this spreadsheet is ultimately going to have a few thousand records. So, I redid the formula like this:

    =SUM(IF(FREQUENCY(MATCH(A2:A5000,A2:A5000,0),ROW(A2:A5000)-ROW(A2)+1)=1,1))

    This didn't work. I'm not 100% sure if this formula ignores blank or empty cells.
    So…is this the correct, or best, formula to use for this? If it isn't, where might I find a better one? Thank you very much! Jd
    DL Music 2019.xlsm

    PS: There is a typo in the subject text. I meant WITHOUT Including Duplicates or Blank Cells. My apologies.
    Last edited by jdanniel; 2019-01-12 at 05:53 PM.

  2. #2
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    102
    Articles
    0
    Excel Version
    365
    Hi,
    You can use this array formula =SUM(1/COUNTIF(Table1[Artist],Table1[Artist])). To validate an array formula, type on ctrl+shift+enter instead of enter alone.
    you can check this blog to learn more: https://numidiabi.wordpress.com/2018...istinct-count/

    Hope this solution will help you



    To learn more, visit my personal blog http://numidiabi.wordpress.com
    Last edited by hossat; 2019-01-12 at 07:08 PM.

  3. #3
    Acolyte jdanniel's Avatar
    Join Date
    Jul 2018
    Posts
    55
    Articles
    0
    Excel Version
    2016
    Thank you for replying.

    Question: Why does the formula generate the number 70 when I use ctrl+shift+enter, but I get a decimal when I just press Enter?

    By the way...just out of curiosity...when that UNIQUE function becomes publicly available (I'm using Office 365), would you recommend that function over the formula you gave to me?

    Jd
    Last edited by jdanniel; 2019-01-12 at 07:28 PM.

  4. #4
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    102
    Articles
    0
    Excel Version
    365
    Hi, you're welcome.

    In your example, I'm getting 74!

    If you are in O365, I advise you to try the insider program, it's free and you can get the latest updates of Excel, there are a lot of many nice features, especially the dynamic array functions.


    To learn more, visit my personal blog http://numidiabi.wordpress.com

  5. #5
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    851
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by jdanniel View Post
    the total number of "Artists" but without duplicates or empty/blank cells.
    Try
    Code:
    =SUMPRODUCT((Table1[Artist]<>"")/COUNTIF(Table1[Artist];Table1[Artist]&""))
    or
    =SUMPRODUCT(1/COUNTIF(Table1[Artist];Table1[Artist]&""))-COUNTBLANK(Table1[Artist])
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  6. #6
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    102
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by navic View Post
    Try
    Code:
    =SUMPRODUCT((Table1[Artist]<>"")/COUNTIF(Table1[Artist];Table1[Artist]&""))
    or
    =SUMPRODUCT(1/COUNTIF(Table1[Artist];Table1[Artist]&""))-COUNTBLANK(Table1[Artist])
    Very nice solution, thank you Navic.

Posting Permissions

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