Sumifs By Financial Year and By Months

[hananak]

Hi Everyone,

I have 3 columns, first one is the year and 2nd column is the months no and third column is the Amount. I am trying to calculate sum based on Financial year and whatever months comes under that year. Normally in one Financial year there will be 12 months. I tried sumifs but it is not working not sure why.

Your help will be really appreciated. Below are the screen shots.

Book4
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Year	Months	Amount
2	2019	10	1009.26							Total
3	2019	10	436.38					FY 2019	All 12 months
4	2019	10	6684.14					FY 2020	All 12 months
5	2019	10	1045.88					FY 2021	All 12 months
6	2019	10	29.9					FY 2022	All 12 months
7	2019	10	1500
8	2019	10	62456.64
9	2019	10	39.12
10	2019	10	-1500
11	2019	10	274450
12	2019	10	-62456.64					Drop Down List
13	2019	10	62456.64					Select Year	Select Month	Amount
14	2019	10	166.67					2021	June
15	2019	10	239.26
16	2019	10	83.33
17	2019	10	83.33
18	2019	10	33.33
19	2019	10	3271.42
20	2019	10	37.5
21	2019	10	3640.08
22	2019	10	545.03
23	2019	10	519.92
24	2019	10	4788.58
25	2019	10	1122.54
26	2019	10	400
27	2019	10	50
28	2019	10	100
29	2019	10	2000
30	2019	10	666.67
31	2019	10	4.67
32	2019	10	978.61
33	2019	10	1096.95
34	2019	10	600
35	2019	10	120
36	2019	10	171.08
37	2019	10	519.92
38	2019	10	104.17
39	2019	10	1207.95
40	2019	10	40
41	2019	10	69.87
42	2019	10	-40514.11
43	2019	10	-53808.97
44	2019	10	-274450
45	2019	11	200
46	2019	11	4000
47	2019	11	2415.91
48	2019	11	342.17
49	2019	11	160
50	2019	11	1333.33
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
I14	List	2019,2020,2021,2022
J14	List	April,May,June,July,August,September,October,November,December,January,February,March

 

[hananak]

Some other options for what you may be after?

Thanks. But below are the slight issues:

The first formula is off by one year. The result I am expecting for Year 2019 is appearing in 2020 and 2020 result is appearing on the 2021 and so on.

The 2nd formula it is picking the value but it is considering 10 as month of October but as the Financial Year start from April and ends in March. According to this accounting period cycle, the month 10 is actually January.

Your help will be appreciated.

 

[Peter_SSs]

it is considering 10 as month of October

We didn't have any information earlier to consider it anything but October.

Assuming then that column B represents the Financial Year not Calendar Year, but still guessing a bit ..

22 04 15.xlsm
	I	J	K
1
2			Total
3	FY 2019	All 12 months	8490.53
4	FY 2020	All 12 months	0
5	FY 2021	All 12 months	0
6	FY 2022	All 12 months	0
7
8
9
10
11
12	Drop Down List
13	Select Year	Select Month	Amount
14	2019	February	8451.41
Sum Fy & Month (3)

Cell Formulas
Range	Formula
K3:K6	K3	=SUMIF(B2:B1000,RIGHT(I3,4),D2:D1000)
K14	K14	=SUMIFS(D2:D1000,B2:B1000,I14,C2:C1000,MONTH(EDATE(1&J14,-3)))

 

[hananak]

We didn't have any information earlier to consider it anything but October.

Assuming then that column B represents the Financial Year not Calendar Year, but still guessing a bit ..

Yes column B represents Financial Year.

Thank you very much Peter . That's perfect, exactly the way I wanted .

I really appreciate the time and efforts.

 

[Peter_SSs]

Cheers. Glad we got there in the end.