Extract Monthly Data from a Sales Database (Sum up Sales Value Occurred in Same Date)

[maniknandi]

I have a sales Database as under:

Date Sales Person Vendor Sales Amount
4/1/13 A XYZ Ltd. 4500
4/3/13 B AAA Ltd. 3000
4/3/13 B BBB Ltd. 3000
5/3/13 A BBB Ltd. 2500
6/1/13 B AAA Ltd. 5000

I want to extract data from the above database based on the selection of the month. If I select April' 13 it will show as below:

Date Sales Person Sales Amount
4/1/13 A 4500
4/3/13 B 6000 (Sum up sales amount happened in same date under a salesperson)

Please help me and thanks in advance.

 

[Trevor G]

Look at a Pivot Table or do a sort of the data and then filter based on the month and use the subtotal feature.

 

[maniknandi]

I want to do it with formula...

 

[Trevor G]

Try using a SUMIFS function then. Like this:

Sheet1

*	A	B	C	D	E	F	G	H	I
Date	Sales Person	Vendor	Sales Amount	*	Sales Person	Start Date	End Date	Total
A	XYZ Ltd.	*	A
B	AAA Ltd.	*	B
B	BBB Ltd.	*	*	*	*	*
A	BBB Ltd.	*	*	*	*	*
B	AAA Ltd.	*	*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 75px;"><col style="width: 85px;"><col style="width: 64px;"><col style="width: 93px;"><col style="width: 64px;"><col style="width: 85px;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]01/04/2013[/TD]

[TD="align: right"]4500[/TD]

[TD="align: right"]01/04/2013[/TD]
[TD="align: right"]30/04/2013[/TD]
[TD="align: right"]4500[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]03/04/2013[/TD]

[TD="align: right"]3000[/TD]

[TD="align: right"]01/04/2013[/TD]
[TD="align: right"]30/04/2013[/TD]
[TD="align: right"]6000[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]03/04/2013[/TD]

[TD="align: right"]3000[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]03/05/2013[/TD]

[TD="align: right"]2500[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]01/06/2013[/TD]

[TD="align: right"]5000[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H2 =EOMONTH(G2,0) I2 =SUMIFS($D$2:$D$6,$A$2:$A$6,">="&G2,$A$2:$A$6,"<="&H2,$B$2:$B$6,F2) H3 =EOMONTH(G3,0) I3 =SUMIFS($D$2:$D$6,$A$2:$A$6,">="&G3,$A$2:$A$6,"<="&H3,$B$2:$B$6,F3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

check this youtube video out. Excel Magic Trick 568: SUMIFS function Monthly Running Total from Transaction Data - YouTube

 

[maniknandi]

Thank you so much Trevor,

It is really helpful.

Manik

 

[Trevor G]

Happy to help, thanks for letting me know it helped you find a solution you are happy with.