Distinct count in Excel

[gard0128]

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.

 

[doxiepup]

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

=count(range) will count numbers

=counta(range) will count words

 

[gard0128]

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

 

[GlennUK]

Have you tried using Pivottables?

Cheers, Glenn.

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

 

[Fae]

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.

 

[DPlank]

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!

 

[bluedragon2]

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

 

[Fae]

Pivot might well be one of the better options

Miracles we do immediately ...
imposibilities take a little longer.