Sum by month/year

ace09

New Member
Joined
Aug 28, 2018
Messages
3
Hi there,

I have a date column and a Dollar column. I want to sum based on the month and year. So in the below example for Jan 2019 i would get the answer of $2300. I believe the Sum Production or Sum if function needs to be used. Can any one help with this one?
Date$ value
01-01-2019500
01-02-2019300
01-20-20191500
02-05-20192000
05-07-20194000
15-07-2019200

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Ray Bailey

New Member
Joined
Nov 12, 2015
Messages
34
Hi,

If you're able to add an extra column the easiest way would be to have a column with formula =EOMONTH(A2,0) the do SUMIF on that column.


Cheers

Ray
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Assuming those are Real Date values in Column A (the last entry does Not match the rest), use SUMPRODUCT to check for Month:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">$ value</td><td style="text-align: right;;"></td><td style=";">Month to Sum</td><td style=";">Result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1/1/2019</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1/2/2019</td><td style="text-align: right;;">300</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1/20/2019</td><td style="text-align: right;;">1500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2/5/2019</td><td style="text-align: right;;">2000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">5/7/2019</td><td style="text-align: right;;">4000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">7/15/2019</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet212</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">MONTH(<font color="Green">A2:A7</font>)=D2</font>)*B2:B7</font>)</td></tr></tbody></table></td></tr></table><br />

Change D2 to the Number corresponding to whichever Month you want to SUM.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,139
Messages
5,527,054
Members
409,741
Latest member
serfr

This Week's Hot Topics

Top