Using SUMPRODUCT to count unique numbers within a date range

**was** · 02-23-2005, 06:21 PM

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.

**Domenic** · 02-24-2005, 09:20 AM

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.

**was** · 02-24-2005, 01:46 PM

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

**was** · 02-24-2005, 05:41 PM

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.

**Domenic** · 02-24-2005, 06:38 PM

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!

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.