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
=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...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.