Obtain separate monthly totals from a year's daily data.

vintage36

Board Regular
Joined
Nov 29, 2011
Messages
58
I have daily data over a year in col B with the dates in col A. I wish to find the sum of data for each month, (1st to last day). I had thought of sumifs, but cannot see how that could work, given the different months’ lengths.
What would be the best easiest way to get the totals I want, please?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
If you only have a years worth of data, try
=SUMPRODUCT((MONTH($A$2:$A$100)=ROW(A1))*($B$2:$B$100))
and drag down
 
Upvote 0
If you only have a years worth of data, try
=SUMPRODUCT((MONTH($A$2:$A$100)=ROW(A1))*($B$2:$B$100))
and drag down
I get only get figures for months 1-3 and part of month 4, i.e. for cells B2 to B100. I tried altering B100 to B366 in your suggested formula, but the results remain the same and the formula has reverted to what you suggested. I assume I am getting something wrong. Please can you advise?
 
Upvote 0
How about
=SUMPRODUCT((MONTH($A$2:$A$366)=ROW(A1))*($B$2:$B$366))
 
Upvote 0
You can actually avoid formulas by manipulating little bit of your simple data (add extra colmns - 2), then use Sub Totals under Data as in:
At each Change in Month (Jan-Dec), Count, Column E/F
Then Click (+) or (-)
 
Upvote 0
Sumifs works completely fine for this.
Add the value column as the sumrange then use the datecolumn for the first criteria range. For the first criteria use the bigger or equal signs inside quotes then & then eomonth function and add the date on that row as first argument. Second argument put in -1. Outside eomonth put +1. This will then say add from first of this month.
Then add the same criteria range plus the same criteria but instead of -1 as second eomonth argument set it to 0, also leave out the last +1. Also change criteria logic to less or equal than.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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