# Thread: Count Distinct (XP)

1. ## Count Distinct (XP)

Hi

I've been going round in circles with this all morning and have run out of ideas! I want a distinct count of values but without using a pivot table because I need to restrict the values the pivot table looks at to the current year. Now appreciate I could do this by adjusting the range each time but I've got a couple of hundred of these things to sort out so something that calculates automatically would be very welcome.

I need to count the number of distinct purchases according family membership (i.e. whether son, daugther, wife etc) over a period of time and sum total:

Sort of: This year two customers were 'Wives' who spent

2. ## Re: Count Distinct (XP)

<P ID="edit" class=small>(Edited by sdckapr on 30-Jun-05 07:34. Added PS)</P>I think a pivot table could work. You could use the date/year as page field and would summarize based on this year selected.

The other option that comes to mind is using Array Formulas (this is an excellent primer from Chip Pearson).

If you need further help, please elaborate a little on what you need, perhaps attach an example sheet...

Steve
Auto-Filters with subtotal function could be used. And D-functions might also be a useful tool...

3. ## Re: Count Distinct (XP)

Hi

Both Array and Pivot suggestions worked fine. I've gone with the Array solution as I can extend the ranges so the formula doesn't keep having to be re-set.

Many thanks for this, I have used Arrays before but did not know how to construct one as an 'OR' so Chip Pearson's info most instructive.

4. ## Re: Count Distinct (XP)

If you use a dynamic range (eg with offset) for the pivot range source, it will always be "up-to-date"

Chip has an article on Dynamic Named Ranges also.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•