Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •