Summing daily data by month

[AlphaSkidz]

In column A, starting at cell A2, I have dates that start with 4/1/2013 and goes until 5/30/2013. Data will continue to be entered daily, so this column will grow. In Column B, starting at cell B2, I have total calls for the specific date. In Cell W2 I have "Apr-13", in W3 I have "May-13", and this column will continue to grow as well. I want a formula that will be able to summarize daily data by month, and eventually be able to tell the difference between Apr-13 and Apr-14.

[dilipandey]

Hi Alphaskidz,

you can use a pivot in this query.. see attached:-
Sum by Month.xlsx


Regards,
DILIPandey
<click on below * if this helps>

[Tony Valko]

One way...

Entered in X2 and copied down:

=SUMIFS(B:B,A:A,">="&W2,A:A,"<="&EOMONTH(W2,0))

[AlphaSkidz]

What happens when we start adding data for June, July, etc? I don't want to have to continue to update the pivot table. Also, this summarized information we be used as data for charts.

[AlphaSkidz]

Thanks Tony Valko , this worked perfectly. Would you be able to walk me through the syntax of this formula so I understand it?

[Tony Valko]

Sure...

=SUMIFS(B:B,A:A,">="&W2,A:A,"<="&EOMONTH(W2,0))

We want to sum values in column B that fall within specific date boundaries in column A.

B:B is the sum range

A:A are the dates

">="&W2 is the lower date boundary.

W2 contains the date 4/1/2013. So the lower date boundary is dates greater than or equal to 4/1/2013.

"<="&EOMONTH(W2,0) is the upper date boundary.

The EOMONTH function returns the End Of Month date that is n months from the referenced date. The referenced date is 4/1/2013 in cell W2. n months = 0 in this case so EOMONTH returns the end of month date for Apr 2013 which is 4/30/2013.

So, sum the values in column B that correspond to dates in column A that are greater than or equal to 4/1/2013 AND are less than or equal to 4/30/2013.

=SUMIFS(B:B,A:A,">="&W2,A:A,"<="&EOMONTH(W2,0))