SOLVED

# Does Excel have Count Distinct options

Copper Contributor

# Does Excel have Count Distinct options

I know the ability to countdistinct numbers exists in Power Query and other tools but can it be done in Excel? I only work in excel and have not had good luck learning Power Query.  I am just an old Vlookup guy and it does everything I need to do.  I do have the need for countdistinct values in formulas but have not found it in Excel. Appreciate any help here.

Thaks

Tommy C

6 Replies

# Re: Does Excel have Count Distinct options

``=SUM(1/COUNTIF(B5:B13,B5:B13))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

# Re: Does Excel have Count Distinct options

@Tommy2355 The quick answer is yes, you can now get to that.  A new formula called "unique" exists with office 365 and there's an option to restrict only to records which appear only once.  If you're able to share your workbook I can help get you started with the formula.

best response confirmed by Tommy2355 (Copper Contributor)
Solution

# Re: Does Excel have Count Distinct options

@Tommy2355 Its pretty easier in Excel-365.

``=COUNTA(UNIQUE(A1:A9))``

# Re: Does Excel have Count Distinct options

Outstanding! Thank you!

# Re: Does Excel have Count Distinct options

This one did not work for me but thank you for the reply!

# Re: Does Excel have Count Distinct options

I got the formula in the next post. Thank you for replying!
1 best response

Accepted Solutions
best response confirmed by Tommy2355 (Copper Contributor)
Solution

# Re: Does Excel have Count Distinct options

@Tommy2355 Its pretty easier in Excel-365.

``=COUNTA(UNIQUE(A1:A9))``