Sumifs By Financial Year and By Months

H

hananak

Board Regular
Joined
Feb 10, 2022
Messages
110
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
BCDEFGHIJKLMNOP
1YearMonthsAmount
22019101009.26Total
3201910436.38FY 2019All 12 months
42019106684.14FY 2020All 12 months
52019101045.88FY 2021All 12 months
620191029.9FY 2022All 12 months
72019101500
820191062456.64
920191039.12
10201910-1500
11201910274450
12201910-62456.64Drop Down List
1320191062456.64Select YearSelect MonthAmount
14201910166.672021June
15201910239.26
1620191083.33
1720191083.33
1820191033.33
192019103271.42
2020191037.5
212019103640.08
22201910545.03
23201910519.92
242019104788.58
252019101122.54
26201910400
2720191050
28201910100
292019102000
30201910666.67
312019104.67
32201910978.61
332019101096.95
34201910600
35201910120
36201910171.08
37201910519.92
38201910104.17
392019101207.95
4020191040
4120191069.87
42201910-40514.11
43201910-53808.97
44201910-274450
45201911200
462019114000
472019112415.91
48201911342.17
49201911160
502019111333.33
Sheet1
Cells with Data Validation
CellAllowCriteria
I14List2019,2020,2021,2022
J14ListApril,May,June,July,August,September,October,November,December,January,February,March
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Hi,

Not sure what you mean, may be:

Book3.xlsx
BCDEFGHIJK
1YearMonthsAmount
22019101009.26Total
3201910436.38FY 2019All 12 months
42019106684.14FY 2020All 12 months
52019101045.88FY 2021All 12 months
620191029.9FY 2022All 12 months
72019101500
820191062456.64
920191039.12
10201910-1500
11201910274450
12201910-62456.6Drop Down List
1320191062456.64Select YearSelect MonthAmount
14201910166.672019October39.12
15201910239.26
1620191083.33
1720191083.33
1820191033.33
192019103271.42
2020191037.5
212019103640.08
22201910545.03
23201910519.92
242019104788.58
252019101122.54
26201910400
2720191050
28201910100
292019102000
30201910666.67
312019104.67
32201910978.61
332019101096.95
34201910600
35201910120
36201910171.08
37201910519.92
38201910104.17
392019101207.95
4020191040
4120191069.87
42201910-40514.1
43201910-53809
44201910-274450
45201911200
462019114000
472019112415.91
48201911342.17
49201911160
502019111333.33
Sheet1089
Cell Formulas
RangeFormula
K14K14=SUMPRODUCT((B2:B50=I14)*(C2:C50=MONTH("1/"&J14))*D2:D50)
 
Upvote 0
Some other options for what you may be after?

22 04 15.xlsm
ABCDEFGHIJK
1YearMonthsAmount
22019101009.26Total
3201910436.38FY 2019All 12 months8490.53
42019106684.14FY 2020All 12 months0
52019101045.88FY 2021All 12 months0
620191029.9FY 2022All 12 months0
72019101500
820191062456.64
920191039.12
10201910-1500
11201910274450
12201910-62456.64Drop Down List
1320191062456.64Select YearSelect MonthAmount
14201910166.672019November8451.41
15201910239.26
1620191083.33
1720191083.33
1820191033.33
192019103271.42
2020191037.5
212019103640.08
22201910545.03
23201910519.92
242019104788.58
252019101122.54
26201910400
2720191050
28201910100
292019102000
30201910666.67
312019104.67
32201910978.61
332019101096.95
34201910600
35201910120
36201910171.08
37201910519.92
38201910104.17
392019101207.95
4020191040
4120191069.87
42201910-40514.11
43201910-53808.97
44201910-274450
45201911200
462019114000
472019112415.91
48201911342.17
49201911160
502019111333.33
Sum Fy & Month
Cell Formulas
RangeFormula
K3:K6K3=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))
K14K14=SUM(FILTER(D2:D50,(B2:B50=I14)*(C2:C50=MONTH(DATEVALUE(1&J14))),0))
 
Upvote 0
jtakw said:
Hi,

Not sure what you mean, may be:
Click to expand...

Adding formula for K3:K6

Book3.xlsx
BCDEFGHIJK
1YearMonthsAmount
22019101009.26Total
3201910436.38FY 2019All 12 months8490.53
42019106684.14FY 2020All 12 months0
52019101045.88FY 2021All 12 months0
620191029.9FY 2022All 12 months0
72019101500
820191062456.64
920191039.12
10201910-1500
11201910274450
12201910-62456.6Drop Down List
1320191062456.64Select YearSelect MonthAmount
14201910166.672019October39.12
15201910239.26
1620191083.33
1720191083.33
1820191033.33
192019103271.42
2020191037.5
212019103640.08
22201910545.03
23201910519.92
242019104788.58
252019101122.54
26201910400
2720191050
28201910100
292019102000
30201910666.67
312019104.67
32201910978.61
332019101096.95
34201910600
35201910120
36201910171.08
37201910519.92
38201910104.17
392019101207.95
4020191040
4120191069.87
42201910-40514.1
43201910-53809
44201910-274450
45201911200
462019114000
472019112415.91
48201911342.17
49201911160
502019111333.33
Sheet1089
Cell Formulas
RangeFormula
K3:K6K3=SUMIF(B$2:B$50,RIGHT(I3,4),D$2:D$50)
K14K14=SUMPRODUCT((B2:B50=I14)*(C2:C50=MONTH(1&J14))*D2:D50)
 
Upvote 0
Hmm..., just realized, SUMIFS would work for K14 also, so no need for SUMPRODUCT:

Book3.xlsx
BCDEFGHIJK
1YearMonthsAmount
22019101009.26Total
3201910436.38FY 2019All 12 months8490.53
42019106684.14FY 2020All 12 months0
52019101045.88FY 2021All 12 months0
620191029.9FY 2022All 12 months0
72019101500
820191062456.64
920191039.12
10201910-1500
11201910274450
12201910-62456.6Drop Down List
1320191062456.64Select YearSelect MonthAmount
14201910166.672019October39.12
15201910239.26
1620191083.33
1720191083.33
1820191033.33
192019103271.42
2020191037.5
212019103640.08
22201910545.03
23201910519.92
242019104788.58
252019101122.54
26201910400
2720191050
28201910100
292019102000
30201910666.67
312019104.67
32201910978.61
332019101096.95
34201910600
35201910120
36201910171.08
37201910519.92
38201910104.17
392019101207.95
4020191040
4120191069.87
42201910-40514.1
43201910-53809
44201910-274450
45201911200
462019114000
472019112415.91
48201911342.17
49201911160
502019111333.33
Sheet1089
Cell Formulas
RangeFormula
K3:K6K3=SUMIF(B$2:B$50,RIGHT(I3,4),D$2:D$50)
K14K14=SUMIFS(D2:D50,B2:B50,I14,C2:C50,MONTH(1&J14))
 
Upvote 0
jtakw said:
Adding formula for K3:K6
Click to expand...
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. :confused:
 
Upvote 0
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...
 
Upvote 0
Peter_SSs said:
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. :confused:
Click to expand...

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.
 
Upvote 0
jtakw said:
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...
Click to expand...
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.
 
Upvote 0
hananak said:
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.
Click to expand...
Fair enough. Then perhaps this?

22 04 15.xlsm
BCDHIJK
1YearMonthsAmount
22019101009.26Total
3201910436.38FY 2019All 12 months0
42019106684.14FY 2020All 12 months8490.53
52019101045.88FY 2021All 12 months0
620191029.9FY 2022All 12 months0
72019101500
820191062456.64
920191039.12
10201910-1500
11201910274450
12201910-62456.64Drop Down List
1320191062456.64Select YearSelect MonthAmount
14201910166.672019November8451.41
15201910239.26
1620191083.33
1720191083.33
1820191033.33
192019103271.42
2020191037.5
212019103640.08
22201910545.03
23201910519.92
242019104788.58
252019101122.54
26201910400
2720191050
28201910100
292019102000
30201910666.67
312019104.67
32201910978.61
332019101096.95
34201910600
35201910120
36201910171.08
37201910519.92
38201910104.17
392019101207.95
4020191040
4120191069.87
42201910-40514.11
43201910-53808.97
44201910-274450
45201911200
462019114000
472019112415.91
48201911342.17
49201911160
502019111333.33
Sum Fy & Month (2)
Cell Formulas
RangeFormula
K3:K6K3=SUMPRODUCT(--(IFERROR(YEAR(EDATE(C$2:C$1000&"/"&$B$2:B$1000,9)),0)-RIGHT(I3,4)=0),D$2:D$1000)
K14K14=SUMIFS(D2:D1000,B2:B1000,I14,C2:C1000,MONTH(1&J14))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Calc cumulative by month
Replies
6
Views
194
mylamly26
M
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
627
Peter_SSs
Peter_SSs
M
  • Solved
SumIf or equivalent
Replies
2
Views
172
Fluff
Fluff
littlepete
  • Question Question
calculating bank interest (basic interest + loyalty interest) per month for three years
Replies
7
Views
498
arun_eagertolearn
A
M
  • Solved
Nested Xlookup not returning all data
Replies
2
Views
426
Mat1968
M

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top