Log In
Register
Home
Active Member
June 25, 2020
Offline
Hi can anyone help me with a formula to get the sum on each month from all sheets.
i have multiple sheets of data, 1 has rent income, another has other income, another has mortgage expenses, another insurance expense, other expenses, etc.
What function can I use to get the sum of all data in 1 sheet if the month from column a matches the month from column a in other sheets.
I tried using sumif and sumifs but it didn’t work.
I'm using a 2013 pc version.
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Take a look at this blog post. I do recommend to gather all data into one Excel table, as you can then use a Pivot Table to sum the numbers.
Br,
Anders
Answers Post
Active Member
June 25, 2020
Offline
The link you've sent of sumproduct function works only with the same dates.
But how can I make that it should give me the sum from all dates if it is the same month.
I tried to do it with date(year(a:a),month(a:a),1) but it didn't let me use this function.
I tried to enter it before the indirect function and also after it but both didn't work.
I want to use a function and not a pivot table.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Your data layout in the sheets was not consistent, which is needed. See attached file, I have added a Pivot Table solution also in the Data sheet, for your reference. In Sum sheet I used the 3D SUMIF as linked to in my previus post.
I hope this helps you to get what you want.
Br,
Anders
Active Member
June 25, 2020
Offline
Your formula doesn't give me the sums from the sheets that the date is not on the 1st day of the month.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Please see updated attached file.
Br,
Anders
Active Member
June 25, 2020
Offline
If this is the only way I'll go with it until I figure out the way to make a new function using VBA and be sure that I certainly will share it here hopefully in the next few days.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
That would be great. There are more than one solution in Excel to get what you want and with VBA you can of course create more.
I hope I gave some useful help. Take care.
Br,
Anders
1 Guest(s)
