How to Count unique entries in Excel

Follow this tutorial to learn how to calculate unique entries existing in a column in Excel to get a clearer picture of your entered data.

Microsoft Excel, a commercial spreadsheet application allows calculations, provides graphing, tools, macro programming and pivot tables. You can use formulas for not only calculation but also to help you in other ways. In order to find the unique entries in Excel, a certain formula has to be used which helps in excluding counting of duplicate data entries.

Follow this step by step tutorial to learn how to count unique entries in Excel.


Step # 1 -- Put in the formula

Let's suppose, we want to know how many departments are there in our company just by looking at the sheet or in how many states do we have our offices in. It might take very long if you have a large amount of data and counting manually may take ages. We have a function which will help you to count these unique entries.
So at the top of the sheet, insert a new row and type

Is equals to sum bracket open 1 forward slash bracket open count if bracket open B3 colon B37 comma B3 colon B37 and then bracket close three times

"b3" is the first cell that has data in it and "b37" is the last. After typing "b3" in the function, you can press "shift + ctrl + down arrow" to automatically enter the last cell name in the function.

Step # 2 -- Calculate unique entries

After you have typed in the function, press "ctrl + shift + enter" and the number of unique data entries will be calculated. You can drag the function to other columns as well.

Step # 3 -- Add some new unique entries

Let's see what will happen if we add three new departments to the list. The function will re-calculate and it will show a total of 10 departments now. This means that within the specified range, the number of unique entries gets re-calculated whenever you make a change in it.

Now you can count the unique entries in Excel easily to get a clearer view of your data.

[howtechoffice]

❗ Subscribe To Our Main YouTube Channel

[sramya]

Thank u.u saved me today from my work.my lead told me to find the distinct values count in a column.but I really don't hv any idea how to do that.quickly came to utube and found ur video.thank u :)

[yrot]

excellent video, straight to the point, no bs.. that is exactly what I need.. have to re-watch several times b/c you speak a little fast.. thank you very much for sharing your expertise.

[harish.d]

TYPE =SUM(1/(COUNTIF(A5:A17, A5:A17)))CTRL+SHIFT+ENTER

[jahenders]

This is well presented, but the formula doesn't work for me in a very similar instance

[zxexrxox]

cool video. This is very helpful. I was wondering if you could break down exactly what each part in the formula is doing? I like knowing how to do things but I also love understanding the whole process of what each component is doing, and contributing to the whole picture.
Thanks

[badew]

Be sure to press CTRL+SHIFT+ENTER when you typed in the formula (it should then appear in curly brackets). Otherwise itl'll display #DIV0

[fieldhockey]

You can also use this to count unique entries in a column if another column equals a certain value. In the video example, how many departments do I have in Pennsylvania (PA)?

Change the formula to include an IF statement before the Countif portion: =SUM(IF(C3:C37="PA", 1/(COUNTIF(B3:B37, B3:B37)))

Then be sure to hit CTRL+SHIFT+ENTER since it's an Array formula.

[jmcatada]

Hope parts of the formula were explained for deeper understanding.

[ganpatisunam]

sir your formula is working but i need with subtotal function so plz help me explain with subtotal sum function

[firozuoda]

If we want to count in date wise then how i will do this

[shau]

i thank you for this. Please let me know how this formula work. I will manage to follow because I am learning excel formulas now. thanks again

[vedsoh]

the answer is coming out as 0 (using excel 2016).
Even tried COUNTIFS instead of COUNTIF as I was using this on a column with names.
Any solutions?

[dippyk]

does this have any limitation.I have large data set and this formula aint working on that. Pls let me know

[_TheMax_]

How to count if you select more than defined range? If I have dynamic report that changes number of rows each time I start the report. How to count and avoid empty cells?

[Thoinfocam]

Awesome. You can use this formula too
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3, OFFSET(B3:B37, ROW(B3:B37)-ROW(B3), 0, 1)), MATCH(B3:B37, B3:B37, 0)), ROW(B3:B37)-ROW(B3)+1)>0, 1))}
But your formula is shorter. Thanks for sharing.

[JV-xgjk]

If there are two countifs, how do you use the formula??

[EduardoPiston]

I m following a pick 5 lottery. How do I count how many times each number has been drawn, and the best doubles and triple matches?

[mks]

nice vid, I have noticed if I put any blank cell in middle this formula produces zero. Do you know and how to over come this

[jego]

The formula does not work in older versions of Excel even with Ctrl+Shift+Enter. Works with Office 365. Thank you.