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

vintage36

Board Regular
Joined
Nov 29, 2011
Messages
56
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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,211
Office Version
365
Platform
Windows
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
 

vintage36

Board Regular
Joined
Nov 29, 2011
Messages
56
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,211
Office Version
365
Platform
Windows
How about
=SUMPRODUCT((MONTH($A$2:$A$366)=ROW(A1))*($B$2:$B$366))
 

panyagak

Board Regular
Joined
Feb 24, 2017
Messages
236
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 (-)
 

Swayzy

Board Regular
Joined
Mar 30, 2018
Messages
78
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,047
Messages
5,472,177
Members
406,808
Latest member
Pocher

This Week's Hot Topics

Top