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

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

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.

2. 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.

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

4. 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.

5. Originally Posted by jdanniel
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])```

6. Originally Posted by navic
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
•