Counting unique value with a date range

[ajc623]

I have a sheet with 60k+ lines of date for orders and I am wanting to pull the number of orders with a given date range based on a class of order. For this one I will just use all web orders as what I am looking at. There are several columns of date but the relevant ones are

Col B - date the order was placed
Col C - order #
Col F - Class of order (web)

I am currently using a sumif (see below) to track total dollars within the date ranges on a separate sheet, where I have the dates listed in Col R and the dollars in Col S. the data this formula is on is named 'Sales Date'.

=SUMIFS('Sales Data'!$I:$I,'Sales Data'!$F:$F,"*"&"web"&"*",'Sales Data'!$B:$B,">"&R7,'Sales Data'!$B:$B,"<="&R8)

The problem I run into is on the main data page there is a line for every item ordered, so if someone has 5 items in their web order it shows up 5 times but I would want it to only be counted once.

Any help would be appreciated.

Andrew

 

[AhoyNC]

Maybe something like this:
These are array formulas and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1		Date	Order#			Class			Amount		Start Date	End Date
2		4/9/2017	Order#5			Class2			1		4/11/2017	5/4/2017
3		4/11/2017	Order#12			Class3			2
4		4/11/2017	Order#12			Class3			3		Class =	web
5		4/14/2017	Order#10			web Class1			4
6		4/17/2017	Order#3			Class3			5		Unique
7		4/21/2017	Order#8			Class2			6		Count
8		4/23/2017	Order#15			Class3			7		2
9		4/24/2017	Order#16			webClass1			8
10		5/1/2017	Order#16			webClass1			9		Sum
11		5/3/2017	Order#18			Class3			10		Unique
12		5/4/2017	Order#16			Class1			11		12
13		5/14/2017	Order#14			Class2			12
14		5/23/2017	Order#17			Class2			13
15		5/26/2017	Order#2			Class2			14
16		6/3/2017	Order#13			Class1			15
17		6/5/2017	Order#6			Class3			16
18		6/10/2017	Order#19			Class4			17
19		6/12/2017	Order#7			Class1			18
20		6/13/2017	Order#11			Class2			19
CTRL-SHIFT-ENTER.

 

[Aladin Akyurek]

The SUMIFS formula you posted does not seem to agree on the ranges you describe. For example: SUMIFS is summing 'Sales Data'!$I:$I while you say "the dollars in Col S." Care to clarify? Even better post a tiny sample with the expected results...

 

[ajc623]

Aladin, below is a sample of what i am wanting to do. I added all the columns that are in the actual data sheet but only populated the relevant ones. Basically I would like to have a way to pull a total quantity of web orders (one example) within a given date range (most likely monthly) The problem is that if multiple products are in a single order then that order "Num" will show up multiple times. I looked at pivot tables but could not see a way to do this and was wondering if i could somehow do it like i have shown in column K & L. This would count the number of unique values under the "Num" column given that it fell between two dates, for the Qty "2" it would be >K2&<=K3 and for Qty "3" >K3&<=K4, and that the "Class" column contained the text string "web" ( "*"&"web"&"*" ). Thanks

Andrew

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item	Date	Num	Memo	Name	Class	Qty	Sales Price	Amount	*	Date Range	Qty
2	*	5/25/2017	1	*	*	WEB	*	*	*	*	4/30/2017	*
3	*	5/25/2017	2	*	*	PHONE	*	*	*	*	5/31/2017	2
4	*	5/27/2017	3	*	*	WEB	*	*	*	*	6/30/2017	3
5	*	5/27/2017	4	*	*	REP	*	*	*	*	*	*
6	*	6/1/2017	5	*	*	WEB	*	*	*	*	*	*
7	*	6/1/2017	5	*	*	WEB	*	*	*	*	*	*
8	*	6/1/2017	6	*	*	REP	*	*	*	*	*	*
9	*	6/5/2017	7	*	*	WEB	*	*	*	*	*	*
10	*	6/5/2017	8	*	*	PHONE	*	*	*	*	*	*
11	*	6/7/2017	9	*	*	WEB	*	*	*	*	*	*
12	*	6/7/2017	9	*	*	WEB	*	*	*	*	*	*
13	*	6/8/2017	10	*	*	REP	*	*	*	*	*	*
Sheet1

 

[Aladin Akyurek]

In L2 control+shift+enter, not just enter,and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$13-DAY($B$2:$B$13)+1=$K2-DAY($K2)+1,IF(ISNUMBER(SEARCH("web",$F$2:$F$13)),$C$2:$C$13)),$C$2:$C$13),1))