# 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.  Register To Reply

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.  Register To Reply

3. ## Thank you Domenic

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

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.  Register To Reply

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.  Register To Reply

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.  Register To Reply