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

#### vintage36

##### Board Regular
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

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
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
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
=SUMPRODUCT((MONTH(\$A\$2:\$A\$366)=ROW(A1))*(\$B\$2:\$B\$366))

#### panyagak

##### Board Regular
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
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.