How do I do a Count distinct of a column ?

[excelusername]

If I have a table like this, that's like an audit table of dates users run reports.

Report User Date
Fin Fred 1/2/2018
Fin Fred 2/2/2018
Fin John 1/2/2018
HR Fred 3/2/2018
HR Fred 4/2/2018
HR Steve 4/2/2018
HR Jane 4/2/2018

And I want to calculate the number of Users that have used each report, to produce

Report #Users
Fin 2
HR 3

Or number of users that have used each report each day

Report Date #Users
Fin 1/2/2018 2
Fin 2/2/2018 1
HR 3/2/2018 1
HR 4/2/2018 3

All help much appreciated! Thanks

 

[Michael M]

Try setting up a pivot table to do this !
Highlight the data
Insert Tab
Pivot Table
Report column will be the filter
Row label will be the users
And dates will be the data

 

[Fazza]

Maybe distinct counts are available in pivot tables in latest Excel versions, I don't know.

If not, suggest you account for that requirement when creating the pivot tables. This is available in all Excel versions with pivot tables,
- first PT. SELECT DISTINCT Report, User FROM yourtable
- 2nd PT. SELECT DISTINCT Report, [Date], User FROM yourtable

Date name for field can be problematic. Hopefully enclosing Date in brackets will suffice. If not next step up is to include table reference. Such as
SELECT DISTINCT T.Report, T.[Date], T.User FROM table T

Instead of pivot tables, you can use query tables, btw. Either way these approaches will work well with huge datasets (that may be too slow with formulas).

cheers

 

[Fazza]

This well presented data covers the topic has comment on the subject, and provides other solutions.

http://www.contextures.com/pivottablecountunique.html

The pivot a pivot approach is one I'd forgotten! Haven't done that for many years.