# Using SUMPRODUCT to count unique numbers within a date range

1. ## 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. 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!

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. ## Thank you Domenic

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

4. ## 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.

5. 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

d) Edit > Paste Special > Add > OK

Hope this helps!

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.

6. ## Thank you Domenic

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

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

#### 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