Calculation by Months

[akber.431]

Hi,

I have the receipt voucher details like Date,RV#,Amount,

i just to want to calculate the sum of Month, with the sum if or any other can anyone guide me how i can do that.

for eg,
01-04-2011 00001 12000
02-04-2011 00002 15000
03-04-2011 00003 17000
04-05-2011 00004 12000
05-05-2011 00005 10000
01-06-2011 00007 12000


formula Required for the following months

April(Collected AMount)
May(Collected AMount)
June(Collected AMount)

 

[Aladin Akyurek]

Let E2 house 1-Apr-11, formatted as mmmm to display April.

If on Excel 2007 or later...

=SUMIFS(C2:C7,A2:A7,">="&E2,A2:A7,"<="&EOMONTH(E2,0))

Otherwise:

=SUMIF(A2:A7,">="&E2,C2:C7)-SUMIF(A2:A7,">"&EDATE(E2,1))

The latter requires that the Analysis Toolpak add-in must be active on versions prior to 2007.

 

[akber.431]

Sorry,
its not working the and what is E2

 

[Aladin Akyurek]

Sorry,
its not working the and what is E2

E2 is the cell which hold the month criterion in the form of a month/year date, expressed as a first day date:

E2: 1-Apr-11
E3: 1-May-11

etc.

You are expected to enter the formula in F2 and copy it down.

 

[snoopyhr]

Here is another approach, it seems to be working

Excel Workbook
	A	B	C	D	E	F
19	Date	RV#	Amount		Month of interest	Total for the month
20	1/4/2011	000001	12000		Jan	39000
21	1/31/2011	000002	15000		Mar	17000
22	3/4/2011	000003	17000		Apr	12000
23	4/5/2011	000004	12000		May	10000
24	5/5/2011	000005	10000
25	1/6/2011	000007	12000
Sheet6

 

[akber.431]

sorry,

still its not working yet. and how can i prefix flower bracket before the equal to.

 

[snoopyhr]

Do not know if your response is toward my solution or not but in general :

if you are referring to {...} . this a symbol or indication for ARRAY type formula.
instead of confirming the formula with enter, you need to confirm it with Ctrl Shift Enter at the same time.

 

[akber.431]

Thanks dear,


but i when i select the month its coming repeat along with cell
for eg.

May 118513
May 118513
May 118513
May 118513
May 118513
May 118513
May 118513
May 118513
May 118513
May 118513
May 118513
May 118513
i just want to keep one cell for each month

 

[snoopyhr]

Hope this clear things up a bit...

Excel Workbook
	A	B	C	D	E	F	G
1	Date	RV#	Amount		Month of interest	Total for the month
2	1/4/2011	000001	12000		JAN	39000
3	1/31/2011	000002	15000		Feb	0	No data for Feb
4	3/4/2011	000003	17000		Mar	55149
5	4/5/2011	000004	12000		Apr	51144
6	5/5/2011	000005	10000		May	36860
7	1/6/2011	000007	12000		Jun	13341
8	8/30/2011	000007	14218		Jul	12893
9	12/9/2011	000007	14767		Aug	14218
10	5/21/2011	000007	14844		Sep	0	No data for Sep
11	4/6/2011	000007	12078		Oct	26036
12	11/3/2011	000007	14790		Nov	14790
13	12/24/2011	000007	13191		Dec	68473
14	4/28/2011	000007	13242		Jan	39000
15	10/6/2011	000007	12505		Feb	0	No data for Feb
16	3/11/2011	000007	12074		Mar	55149
17	12/17/2011	000007	12785
18	5/28/2011	000007	12016		Or use data validation:
19	7/19/2011	000007	12893		and copy the validation down
20	6/3/2011	000007	13341		Month	Total for the month
21	10/3/2011	000007	13531		Feb	0
22	12/29/2011	000007	13068		Mar	55149
23	4/1/2011	000007	13824		May	36860
24	3/29/2011	000007	12923		Aug	14218
25	12/24/2011	000007	14662
26	3/18/2011	000007	13152
Sheet11

#VALUE!