+ Reply to Thread
Results 1 to 6 of 6

Function including a distinct count

  1. #1
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Function including a distinct count

    Greetings,

    I have a spreadsheet with a tab "Data" that looks like this:

    A B C D
    System ID Date Amt Category
    151398 1/1/08 10.00 Online - Website
    151398 1/1/08 90.00 Online - Website
    151399 1/1/08 30.00 Online - Website

    How can I modify the following function to only count the following as 2 gifts (considering two of them have the same system ID)?

    =SUMPRODUCT(--(Data!$C:$C= "Online Giving - Website"),--(Data!$A:$A>DATE(2008,1,1)),--(Data!$A:$A<=DATE(2008,1,31)))

    If someone can help me out, as you have done so graciously before, that would be awesome. Otherwise I may severely SOL.

    Thanks as always,
    Bryce
    Last edited by bkatzman; 05-19-2010 at 04:13 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Function including a distinct count

    Are the column references in your formula the same as your sample? Looks like you added column A and the rest of the columns moved over 1....

    If so, try:

    Please Login or Register  to view this content.

    confirmed with CTRL+SHIFT+ENTER
    not just ENTER. Adjust ranges if necessary.

    Note: with these array formulas, includind Sumproduct, you should avoid whole column referencing as these formulas are resource intensive.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Re: Function including a distinct count

    Thanks for the your help. I got zero as a result. Attached is a sample to answer your question about headers/referencing.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Function including a distinct count

    Try something like:

    Please Login or Register  to view this content.
    I used a defined range of row 2 to row 10.. adjust as necessary. Remember to confirm with CTRL+SHIFT+ENTER

    Also you had >DATE(2008,1,1) when you wanted probably >=DATE(2008,1,1)

  5. #5
    Registered User
    Join Date
    11-10-2009
    Location
    Watkins Glen, NY
    MS-Off Ver
    Excel 2007/2003
    Posts
    48

    Re: Function including a distinct count

    Thanks again. Your code was awesome. Unfortunately as you alluded to it will require lots of processing. So I need to find another solution.

    Thanks for catching the greater than or equal to issue!

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Function including a distinct count

    Perhaps you can make it more dynamic like this.

    In a free cell, say E1, enter:

    =MATCH(9.999999E+307,A:A)

    this tells us that last row a numeric entry is made in column A.

    Then try:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER

    this will adjust the ranges to cover from A2 to the last row indicated in E1 and hopefully make it a bit less resource intensive.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1