I want date wise item wise summary report

[Prodip Das]

I want to generate date wise item wise quantitative summary report. But not able to judge the appropriate formula. Would appreciate if any one can help me out to sort out the same.Find below the table.

Date	Buyer's Name	Item	Invoice No	Unit	Qty
01-04-2015	Ramesh & Co	Full Plate	1	Nos.	40
01-04-2015	Ramesh & Co	Qtr Plate	1	Nos.	50
01-04-2015	Ramesh & Co	Tumbler	1	Nos.	50
01-04-2015	Prabir Das	Full Plate	2	Nos.	50
01-04-2015	Prabir Das	Tumbler	2	Nos.	50
04-04-2015	Kamal Traders	C/Saucer Set	3	Set	10
04-04-2015	Kamal Traders	Soup Bowl	3	Nos.	50
04-04-2015	Ganesh Acharya	Bowl-Small	4	Nos.	60
04-04-2015	Ganesh Acharya	C/Saucer Set	4	Set	30
04-04-2015	Ganesh Acharya	Soup Bowl	4	Nos.	150
04-04-2015	Vicky Shaw	Qtr Plate	5	Nos.	100
04-04-2015	Sanjay Dey	Tumbler	6	Nos.	70
04-04-2015	Bharati Udyog	Bowl-Small	7	Nos.	100

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

I want to get report using appropriate formula as per following:

Date	Item	Qty
01-04-2015	Full Plate	90
01-04-2015	Qtr Plate	50
01-04-2015	Tumbler	100
04-04-2015	C/Saucer Set	40
04-04-2015	Soup Bowl	200
04-04-2015	Bowl-Small	160
04-04-2015	Qtr Plate	100
04-04-2015	Tumbler	70

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[konew1]

A pivot table will do this for you

 

[Aladin Akyurek]

If you insist on having a formula system...

Sheet1

Row\Col	A​	B​	C​	D​	E​	F​
1​	Date	Buyer's Name	Item	Invoice No	Unit	Qty
2​	1/4/2015​	Ramesh & Co	Full Plate	1​	Nos.	40​
3​	1/4/2015​	Ramesh & Co	Qtr Plate	1​	Nos.	50​
4​	1/4/2015​	Ramesh & Co	Tumbler	1​	Nos.	50​
5​	1/4/2015​	Prabir Das	Full Plate	2​	Nos.	50​
6​	1/4/2015​	Prabir Das	Tumbler	2​	Nos.	50​
7​	4/4/2015​	Kamal Traders	C/Saucer Set	3​	Set	10​
8​	4/4/2015​	Kamal Traders	Soup Bowl	3​	Nos.	50​
9​	4/4/2015​	Ganesh Acharya	Bowl-Small	4​	Nos.	60​
10​	4/4/2015​	Ganesh Acharya	C/Saucer Set	4​	Set	30​
11​	4/4/2015​	Ganesh Acharya	Soup Bowl	4​	Nos.	150​
12​	4/4/2015​	Vicky Shaw	Qtr Plate	5​	Nos.	100​
13​	4/4/2015​	Sanjay Dey	Tumbler	6​	Nos.	70​
14​	4/4/2015​	Bharati Udyog	Bowl-Small	7​	Nos.	100​

<tbody>
</tbody>

Name A2:A14 Date in Formulas | Name Manager.
Name C2:C14 Item in Formulas | Name Manager.
Name F2:F14 Qty <strike></strike>in Formulas | Name Manager.

Sheet2

<strike></strike>

Row\Col	A​	B​	C​
1​	Date	Item	Qty
2​	1/4/2015​	Full Plate	90​
3​	1/4/2015​	Qtr Plate	50​
4​	1/4/2015​	Tumbler	100​
5​	4/4/2015​	C/Saucer Set	40​
6​	4/4/2015​	Soup Bowl	200​
7​	4/4/2015​	Bowl-Small	160​
8​	4/4/2015​	Qtr Plate	100​
9​	4/4/2015​	Tumbler	70​
10​

<tbody>
</tbody>

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

=IFERROR(INDEX(Date,SMALL(IF(FREQUENCY(MATCH(Item&"|"&Date,Item&"|"&Date,0),
  ROW(Item)-ROW(INDEX(Item,1,1))+1),ROW(Item)-ROW(INDEX(Item,1,1))+1),
  ROWS(A$2:A2))),"")

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

=IFERROR(INDEX(Item,SMALL(IF(FREQUENCY(MATCH(Item&"|"&Date,Item&"|"&Date,0),
  ROW(Item)-ROW(INDEX(Item,1,1))+1),ROW(Item)-ROW(INDEX(Item,1,1))+1),
  ROWS(B$2:B2))),"")

In C2 just enter and copy down:

=IF($A2="","",SUMIFS(Qty,Date,A2,Item,B2))

<strike></strike>

 

[Prodip Das]

Hi Aladian its just Wow...thanks a lot..

 

[Aladin Akyurek]

Hi Aladian its just Wow...thanks a lot..

You are welcome.