To get delivered qty of a specified item in a specified period without using filter withmonthwise separation

[jose_valooran]

I have a problem like, as in a template, there will be month names (Jan -Dec) in rows say from A10-Jan to A21-Dec. In Cells B10 to B21, I need sum of values(sum of qty) which fall in the period(beginning date and end date will be entered in say B2 & B3)against the selected Item which will be selected from a List in Cell B4.
For eg:I have a data sheet-1 like this.
Item Qty Date
A 2 01-01-2011
B 3 02-03-2011
C 2 25-01-2011
D 3 25-01-2011
B 2 29-03-2011
D 5 26-02-2011
B 2 06-01-2011
B 3 28-01-2011

I need the result as follows. If in Sheet 2, I give a period and Item name, how to get the sum of Qty of the item against each month in the period given?
eg:Sheet 2 should look as follows.Dates and Item name I hve to give.
Period
From 01-01-2011
To 28-02-2011
Item B
Qty-Sum
Jan 5
Feb 0
March
April
May
June
July
August
Sep
Oct
Nov
Dec

Could you please help me to get the formulas for getting the result sheet like this? Thanks in advance.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

You could use the formula shown below in Sheet2!B8, copied down to the other cells in Column B.

Excel Workbook
	A	B	C
1	Item	Qty	Date
2	A	2	01/01/2011
3	B	3	02/03/2011
4	C	2	25/01/2011
5	D	3	25/01/2011
6	B	2	29/03/2011
7	D	5	26/02/2011
8	B	2	06/01/2011
9	B	3	28/01/2011
Sheet1

Excel Workbook
	A	B
1		Period
2	From	01/01/2011
3	To	28/02/2011
4
5	Item	B
6
7		Qty-Sum
8	Jan	5
9	Feb	0
10	Mar	0
11	Apr	0
12	May	0
13	Jun	0
14	Jul	0
15	Aug	0
16	Sep	0
17	Oct	0
18	Nov	0
19	Dec	0
Sheet2

The months in Sheet2 Column A are made from putting the first day of each month in each cell then formatting the cells as "Mmm" to show only the month.

This assumes you have a version of Excel that includes the =EOMONTH function.
If you don't have that function, you can substitute a replacement to calculate the end of each month.