Using SUMPRODUCT to count unique numbers within a date range

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.

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.