Hi - Dave here.
A problem that comes up often in Excel is how to sum values by month.
The main challenge is to work out the logic needed to target values from the first day of the month through the last day of the month, inclusive.
In the worksheet below, we use the SUMIFS function with the EDATE function to do this. The formula in F5 is:
=SUMIFS(amount,date,">="&E5,date,"<"&EDATE(E5,1))
As the formula is copied down, we get a total for each month listed in column E.
Note: E5:E10 contains first-of-month dates, formatted to display just the month.
[
download the workbook and read the full explanation]
Another way to solve this problem is to use the SUMPRODUCT function and the TEXT function. The logic is simple and elegant. The article explains both approaches in detail and includes the worksheet. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
