Obtaining sets of data that fall on a specific date or withing a specific date range.

[Hamitoco]

Hi,

I have a spreadsheet which contains information for certain sales. Below is a basic representation of my dataset (the actual spreadsheet contains more columns of information and has new entries added daily from multiple people)
'Sales Data' worksheet

Date	ORDER #	Product Code	Qty
10-Oct	102	SKMDRT	5
10-Oct	103	IMRDDT	10
10-Oct	104	PMYTUV	7
10-Oct	105	SKMDRT	3
11-Oct	106	PDYHTW	4
11-Oct	107	FTRHYGQ	15
11-Oct	108	PIMHKLO	5
11-Oct	109	PMYTUV	12
12-Oct	110	IMRDDT	3
12-Oct	111	KJULOIR	2
12-Oct	112	LKJULOIR	2
12-Oct	113	LKJUTYV	9

<tbody>
</tbody>

I would like to know if i could create a formula on another worksheet that will provide me with the product codes and total quantities for a specific date or date range. for example if I'm interested in the 10th October i can insert those date and it will return the following
'Data summary' worksheet

Date	Product Code	qty
10-Oct	SKMDRT	8
10-Oct	IMRDDT	10
10-Oct	PMYTUV	7

<tbody>
</tbody>

Product code SKMDRT appears once with the combined qty for the 2 sales on that date. Or if I'm interested in looking at data form the 10th - 11th it returns

Date	Product Code	QTY
10-Oct	SKMDRT	8
10-Oct	IMRDDT	10
10-Oct	PMYTUV	7
11-Oct	PDYHTW	4
11-Oct	FTRHYGQ	15
11-Oct	PMYTUV	12
11-Oct	PIMHKLO	5

<tbody>
</tbody>

I know the simplest way is to apply a filter to my dataset however i want to avoid this as the sales data has alot of information and is continuously updated, this is why i would like to create a separate sheet which can provide me with a summary for specific dates without affecting the main data. I have also tried a pivot table but that doesn't seem to work for what i am after. i have been reading up about creating an array formula with index/match but have had no luck applying to my data set.

any help would be much appreciated,
Thankyou.

 

[alansidman]

A Pivot Table will do exactly what you want.

v	A	B	C	D	E	F	G	H
1	Date	ORDER #	Product Code	Qty
2	43748	102	SKMDRT	5		Date	Product Code	Sum of Qty
3	43748	103	IMRDDT	10		43748
4	43748	104	PMYTUV	7			IMRDDT	10
5	43748	105	SKMDRT	3			PMYTUV	7
6	43749	106	PDYHTW	4			SKMDRT	8
7	43749	107	FTRHYGQ	15		43749
8	43749	108	PIMHKLO	5			FTRHYGQ	15
9	43749	109	PMYTUV	12			PDYHTW	4
10	43750	110	IMRDDT	3			PIMHKLO	5
11	43750	111	KJULOIR	2			PMYTUV	12
12	43750	112	LKJULOIR	2		43750
13	43750	113	LKJUTYV	9			IMRDDT	3
14							KJULOIR	2
15							LKJULOIR	2
16							LKJUTYV	9
17