Countif unique value within date range

[sinar62]

Hi,
I want to calculate how much unique data within specified date (before than or between).
Assuming the data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Items
[/TD]
[TD]Sales
[/TD]
[TD]Sold on
[/TD]
[/TR]
[TR]
[TD]Tie[/TD]
[TD]Joseph[/TD]
[TD]13-Jun-2013[/TD]
[/TR]
[TR]
[TD]Tie[/TD]
[TD]Susan[/TD]
[TD]1-May-2013[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Joseph[/TD]
[TD]29-Jun-2013[/TD]
[/TR]
[TR]
[TD]Shoe[/TD]
[TD]Susan[/TD]
[TD]14-May-2013[/TD]
[/TR]
</tbody>[/TABLE]

I need the data to calculate only the item category, regardless of the salesperson. For example:
1. Count of category of item sold within 05/01/2013 till 06/15/2013 = 2 (1 tie & 1 shoe)
2. Count of category of item sold after 04/15/2013 = 3 (1 tie, 1 shirt, 1 shoe)

Please help me with the excel formula, if possible without modifying the original data.
Thank you guys!

 

[sinar62]

Hi, already found the solutions

I mistype the wrong example for number two. It should be item sold after 06/15/2013 = 3.

Here is the array formula for example 1:
=SUM(--(FREQUENCY(IF(((C1:C4<=DATE(2013, 6, 15)*(C1:C4>=DATE(2013, 5, 1)))), COUNTIF(A1:A4, "<"&A1:A4), ""), COUNTIF(A1:A4, "<"&A1:A4))>0))
Array formula for example 2:
=SUM(--(FREQUENCY(IF(((C1:C4<=DATE(2013, 6, 15))), COUNTIF(A1:A4, "<"&A1:A4), ""), COUNTIF(A1:A4, "<"&A1:A4))>0))

As the above formulas are array formula, after typing the formula click CTRL+SHIFT+ENTER.

 

[Aladin Akyurek]

Are you trying to count the distinct items sold?

 

[sinar62]

Yes. Please let me know if there's simpler formula.

 

[Aladin Akyurek]

Yes. Please let me know if there's simpler formula.

E1: 05/01/2013

F1: 06/15/2013

G1: 04/15/2013

1. Distinct count of items sold between dates in E1 and F1...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$5<>"",
  IF($C$2:$C$5>=$E1,IF($C$2:$C$5<=$F1,
  MATCH("~"&$A$2:$A$5,$A$2:$A$5&"",0)))),
  ROW($A$2:$A$5)-ROW($A$2)+1),1))

2. Distinct count of items sold after the date in G1...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$5<>"",
  IF($C$2:$C$5>=$G1,MATCH("~"&$A$2:$A$5,$A$2:$A$5&"",0))),
  ROW($A$2:$A$5)-ROW($A$2)+1),1))