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

jdanniel

Member
Joined
Jul 16, 2018
Messages
84
Reaction score
0
Points
6
Excel Version(s)
MS365
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
View attachment 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:
Last edited:
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:
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
 
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])
 

Attachments

  • DLMusic2019-navic9723.xlsm
    66.9 KB · Views: 15
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.
 
Back
Top