Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Distinct count in Excel

Status
Not open for further replies.

gard0128

Technical User
Jun 18, 2001
38
US
I am attempting to do a distinct count of a list in Excel.

Is there a function that handles this?

Any suggestions are appreciated.

Thanks.
 
Could you please explain what you mean by "distinct"?

=count(range) will count numbers

=counta(range) will count words
 
I have a list of repeating values. I am trying to count the number of salespeople in each region that have sales for a specific product. They only show up in the list if they have a sale. I am doing more calculation on this list, so I'd don't want to change the format of the list.

Field 1: Region (repeating value)
Field 2: SalesPerson (repeating value)
Field 3: Sales of each product
 
Have you tried using Pivottables?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Try This

=dcount(database,field,criteria)
database = all the data (everything)
field = the Product
criteria = the sales figures

but if you want as you say only need to see them in the list if they have a sale, check out the help on MS Excel related to database functions - DCOUNT




Miracles we do immediately ...
imposibilities take a little longer.
 
1. Highlight your data
2. Data -> Filter -> Advanced Filter
3. Make sure the List range box details the range of the first set of repeating values
4. Check the box for Unique Records Only
5. Click OK.

This will show you the distinct list for the first set (by Region).

Copy and paste this on another sheet.

Repeat 1-5 on this output to get the distinct list for each salesperson.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!
 
Also, SUMPRODUCT will also give you a count, but Pivot Table is a good way to go

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Pivot might well be one of the better options

Miracles we do immediately ...
imposibilities take a little longer.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top