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