+ Reply to Thread
Results 1 to 6 of 6

Using SUMPRODUCT to count unique numbers within a date range

  1. #1
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Arrow Using SUMPRODUCT to count unique numbers within a date range

    I am trying to count unique numbers within a date range. Where column A containes a list of dates and column B contains a list of numbers.

    I used
    =SUMPRODUCT((B5:B317<>"")/(COUNTIF(B5:B317,B5:B317)+(B5:B317="")))
    to count the unique numbers throughout the entire range. But what I would also like to do is narow it down by month.

    I also used
    =SUMPRODUCT(--(D5:D317="Approved"),--(A5:A317>DATE(2004/12/31)),--(A5:A317<DATE(2005/2/0)))
    To count the number of "approved" numbers for a month.

    I would like to use part of each formula to create a new formula that would give me the unique numbers within a date range.

    Any help would be greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try the following...

    =SUM(IF(FREQUENCY(IF((B5:B317<>"")*(A5:A317>=DATE(2005,2,1))*(A5:A317<=DATE(2005,2,28)),B5:B317,""),IF((B5:B317<>"")*(A5:A317>=DATE(2005,2,1))*(A5:A317<=DATE(2005,2,28)),B5:B317,""))>0,1,0))

    OR

    =COUNT(1/FREQUENCY(IF((B5:B317<>"")*(A5:A317>=DATE(2005,2,1))*(A5:A317<=DATE(2005,2,28)),B5:B317,""),IF((B5:B317<>"")*(A5:A317>=DATE(2005,2,1))*(A5:A317<=DATE(2005,2,28)),B5:B317,"")))

    Both of these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

    Hope this helps!

    Quote Originally Posted by was
    I am trying to count unique numbers within a date range. Where column A containes a list of dates and column B contains a list of numbers.

    I used
    =SUMPRODUCT((B5:B317<>"")/(COUNTIF(B5:B317,B5:B317)+(B5:B317="")))
    to count the unique numbers throughout the entire range. But what I would also like to do is narow it down by month.

    I also used
    =SUMPRODUCT(--(D5:D317="Approved"),--(A5:A317>DATE(2004/12/31)),--(A5:A317<DATE(2005/2/0)))
    To count the number of "approved" numbers for a month.

    I would like to use part of each formula to create a new formula that would give me the unique numbers within a date range.

    Any help would be greatly appreciated.

  3. #3
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Thank you Domenic

    I tried the first formula using SUM and it works great. Thank you very much!

  4. #4
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Help please a little problem

    The formula I tried does not give the correct count. I manualy counted the unique numbers for Jan. It was 29 the formula returned 24. Feb. was 33 the formula returned 28. They are both 5 off. Does anyone have an idea what is wrong.

    SUM(IF(FREQUENCY(IF((LIST!B5:B318<>"")*(LIST!A5:A318>=DATE(2005,1,1))*(LIST!A5:A318<=DATE(2005,1,31)),LIST!B5:B318,""),IF((LIST!B5:B318<>"")*(LIST!A5:A318>=DATE(2005,1,1))*(LIST!A5:A318<=DATE(2005,1,31)),LIST!B5:B318,""))>0,1,0))

    Also some of the number sequences in column B start with the letters EC. Non of them are counted either.

    I sure would appreciate any help given.
    Last edited by was; 02-24-2005 at 06:19 PM.

  5. #5
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Since Column B contains or includes alphanumeric values, the formula would have to change to the following...

    =SUM(IF(FREQUENCY(IF((LIST!B5:B318<>"")*(LIST!A5:A318>=DATE(2005,1,1))*(LIST!A5:A318<=DATE(2005,1,31)),MATCH(LIST!B5:B318,LIST!B5:B318,0),""),IF((LIST!B5:B318<>"")*(LIST!A5:A318>=DATE(2005,1,1))*(LIST!A5:A318<=DATE(2005,1,31)),ROW(INDIRECT("1:"&ROWS(LIST!B5:B318))),""))>0,1,0))

    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.


    Also, it's possible that your cells are not formatted the same. Try the following...

    =SUMPRODUCT(--(ISNUMBER(A5:A318)))=COUNTIF(A5:A318,"<>")

    and

    =SUMPRODUCT(--(ISNUMBER(B5:B318)))=COUNTIF(B5:B318,"<>")

    If either of these formulas return FALSE, your cells are not formatted the same. If this is the case, make them the same by doing the following...

    a) Select an empty cell

    b) Edit > Copy

    c) Select your data

    d) Edit > Paste Special > Add > OK

    Hope this helps!

    Quote Originally Posted by was
    The formula I tried does not give the correct count. I manualy counted the unique numbers for Jan. It was 29 the formula returned 24. Feb. was 33 the formula returned 28. They are both 5 off. Does anyone have an idea what is wrong.

    SUM(IF(FREQUENCY(IF((LIST!B5:B318<>"")*(LIST!A5:A318>=DATE(2005,1,1))*(LIST!A5:A318<=DATE(2005,1,31)),LIST!B5:B318,""),IF((LIST!B5:B318<>"")*(LIST!A5:A318>=DATE(2005,1,1))*(LIST!A5:A318<=DATE(2005,1,31)),LIST!B5:B318,""))>0,1,0))

    Also some of the number sequences in column B start with the letters EC. Non of them are counted either.

    I sure would appreciate any help given.
    Last edited by Domenic; 02-24-2005 at 07:14 PM.

  6. #6
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Thank you Domenic

    All is right with the world now! I never would have gotten this done without your help. Thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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