How to chart monthly sums with daily data?

Posted on

QUESTION :

So, I have a table a bit like this:

Date        Total  A    B
01/09/2017  497    262  235
02/09/2017  86     39   47
03/09/2017  118    55   63
04/09/2017  812    404  408
05/09/2017  329    155  174
06/09/2017  583    280  303

but it has over 500 dates. I wanted to chart the sum of the monthly values; without having to sum it in externally, can I put all the daily data in a chart and edit the chart to show the monthly sum?

ANSWER :

You can use SUMIFS():

=SUMIFS(B2:B7,A2:A7,">="&A10,A2:A7,"<="&EOMONTH(A10,0))

If your data is set up like this:

enter image description here

I used 1 January 2017 in A10 (format as you like), which has the formula look for data entered on 1 January through the end of January.

(Note: I’m currently in USA, so my dates are formatted mm-dd-yyyy. You may need to tweak if you’re in Europe/elsewhere.)

Leave a Reply

Your email address will not be published. Required fields are marked *