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

 

[jtakw]

Hi,

Not sure what you mean, may be:

Book3.xlsx
	B	C	D	E	F	G	H	I	J	K
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.6					Drop Down List
13	2019	10	62456.64					Select Year	Select Month	Amount
14	2019	10	166.67					2019	October	39.12
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.1
43	2019	10	-53809
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
Sheet1089

Cell Formulas
Range	Formula
K14	K14	=SUMPRODUCT((B2:B50=I14)*(C2:C50=MONTH("1/"&J14))*D2:D50)

 

[Peter_SSs]

Some other options for what you may be after?

22 04 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Year	Months	Amount
2		2019	10	1009.26							Total
3		2019	10	436.38					FY 2019	All 12 months	8490.53
4		2019	10	6684.14					FY 2020	All 12 months	0
5		2019	10	1045.88					FY 2021	All 12 months	0
6		2019	10	29.9					FY 2022	All 12 months	0
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					2019	November	8451.41
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
Sum Fy & Month

Cell Formulas
Range	Formula
K3:K6	K3	=SUM(FILTER(D$2:D$1000,IFERROR(YEAR(EDATE(DATE(B$2:B$1000,C$2:C$1000,1),-3)),0)-RIGHT(I3,4)=0,0))
K14	K14	=SUM(FILTER(D2:D50,(B2:B50=I14)*(C2:C50=MONTH(DATEVALUE(1&J14))),0))

 

[jtakw]

Hi,

Not sure what you mean, may be:

Adding formula for K3:K6

Book3.xlsx
	B	C	D	E	F	G	H	I	J	K
1	Year	Months	Amount
2	2019	10	1009.26							Total
3	2019	10	436.38					FY 2019	All 12 months	8490.53
4	2019	10	6684.14					FY 2020	All 12 months	0
5	2019	10	1045.88					FY 2021	All 12 months	0
6	2019	10	29.9					FY 2022	All 12 months	0
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.6					Drop Down List
13	2019	10	62456.64					Select Year	Select Month	Amount
14	2019	10	166.67					2019	October	39.12
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.1
43	2019	10	-53809
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
Sheet1089

Cell Formulas
Range	Formula
K3:K6	K3	=SUMIF(B$2:B$50,RIGHT(I3,4),D$2:D$50)
K14	K14	=SUMPRODUCT((B2:B50=I14)*(C2:C50=MONTH(1&J14))*D2:D50)

 

[jtakw]

Hmm..., just realized, SUMIFS would work for K14 also, so no need for SUMPRODUCT:

Book3.xlsx
	B	C	D	E	F	G	H	I	J	K
1	Year	Months	Amount
2	2019	10	1009.26							Total
3	2019	10	436.38					FY 2019	All 12 months	8490.53
4	2019	10	6684.14					FY 2020	All 12 months	0
5	2019	10	1045.88					FY 2021	All 12 months	0
6	2019	10	29.9					FY 2022	All 12 months	0
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.6					Drop Down List
13	2019	10	62456.64					Select Year	Select Month	Amount
14	2019	10	166.67					2019	October	39.12
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.1
43	2019	10	-53809
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
Sheet1089

Cell Formulas
Range	Formula
K3:K6	K3	=SUMIF(B$2:B$50,RIGHT(I3,4),D$2:D$50)
K14	K14	=SUMIFS(D2:D50,B2:B50,I14,C2:C50,MONTH(1&J14))

 

[Peter_SSs]

Adding formula for K3:K6

I'm guessing from the OPs Data Validation list in J14 that their financial year runs from April to March, not January to December?
If so, I'm still not sure whether FY 2019 would be April 2018 to march 2019 or April 2019 to march 2020 so my formulas in K3:K6 might be a year off anyway.

 

[jtakw]

I agree, that's why I didn't initially post any formula for K3:K6, as I just didn't know.
A crucial point that should be explained by OP, was waiting for clarification...

 

[hananak]

I'm guessing from the OPs Data Validation list in J14 that their financial year runs from April to March, not January to December?
If so, I'm still not sure whether FY 2019 would be April 2018 to march 2019 or April 2019 to march 2020 so my formulas in K3:K6 might be a year off anyway.

Thank you for your reply.

Sorry I did not mention it earlier. Our Financial Year runs from April - March. So when you see FY 2019, it actually means April 18 - Mar 19. Another important factor is that the file will be used by different people and some are using Office 2016 and some office 365. so Filter formula might not work for all of us.

 

[hananak]

I agree, that's why I didn't initially post any formula for K3:K6, as I just didn't know.
A crucial point that should be explained by OP, was waiting for clarification...

Thank you for your reply.

Sorry I did not mention it earlier. Our Financial Year runs from April - March. So when you see FY 2019, it actually means April 18 - Mar 19. Because of this the formulas are not picking values as per our Financial Year.

 

[Peter_SSs]

Another important factor is that the file will be used by different people and some are using Office 2016 and some office 365. so Filter formula might not work for all of us.

Fair enough. Then perhaps this?

22 04 15.xlsm
	B	C	D				H	I	J	K
1	Year	Months	Amount
2	2019	10	1009.26							Total
3	2019	10	436.38					FY 2019	All 12 months	0
4	2019	10	6684.14					FY 2020	All 12 months	8490.53
5	2019	10	1045.88					FY 2021	All 12 months	0
6	2019	10	29.9					FY 2022	All 12 months	0
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					2019	November	8451.41
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
Sum Fy & Month (2)

Cell Formulas
Range	Formula
K3:K6	K3	=SUMPRODUCT(--(IFERROR(YEAR(EDATE(C$2:C$1000&"/"&$B$2:B$1000,9)),0)-RIGHT(I3,4)=0),D$2:D$1000)
K14	K14	=SUMIFS(D2:D1000,B2:B1000,I14,C2:C1000,MONTH(1&J14))