Sum Data By Month & Year

[craigs23]

Hi Everyone,

I have searched the forums for an answer to this but to no avail.

I am hoping someone may be able to help me, basically I amtrying to produce a formula that will sum daily data by Month and year.

i.e: if Column A lists dates by day (01/01/2011 through to 25/04/2012)
and Column B lists a number (eg qty of parts).

What I want do is then have seperate cells for each month Say in Column C (i.e Jan 2011-Apr2012) then in the next column have a formula that will basically return a total figure from Coulmn B for the relevant month.

Any help you may be able to offer will be gratefully recieved.

thanks

Craig

 

[WinteE]

Try:

=SUMPRODUCT((MONTH(A1:A100)=4)*(B1:B100))

.. where dates are in range A1:A100 and values are in range B1:B100 this will give you the total of April (MONTH() = 4).

 

[VoG]

With month number (1 for January) in C1 and year (e.g. 2011) in D1 try

=SUMPRODUCT(--(MONTH(A1:A100)=C1),--(YEAR(A1:A100)=D1),B1:B100)

 

[craigs23]

Hi Guys,

Thanks for your quick response, WintE your solution worked up to a point but thanks anyway.
VoG Your solution was exactley what I was looking for, Thank you!!

One more question in VoG's formula what is the function of the hyphons?eg SUMPRODUCT(--(
I am fairly new to this level of formula but am trying to lear as much as possible, while it is fantastic that people on the forum can offer solutions I would really like to try and understand what the formulas/codes are actually doing so any help is appreciated.

Once again Thanks for the quick repsonse guys!!

 

[VoG]

For example --(MONTH(A1:A100)=C1) returns an array of False/True values. -- coerces that into an array of 0/1 that SUMPRODUCT can process.

More info:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mrexcel.com/forum/showpost.php?p=337954&postcount=8

 

[craigs23]

For example --(MONTH(A1:A100)=C1) returns an array of False/True values. -- coerces that into an array of 0/1 that SUMPRODUCT can process.

More info:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.mrexcel.com/forum/showpost.php?p=337954&postcount=8

Thats made it a lot Clearer!!

Thanks VoG!!

 

[navafolk]

Hi,
I try SUMPRODUCT for sum data by month and year but it just shows #VALUE :

=SUMPRODUCT(--(Table1[[#All],[Date issue]]=A2),--(MONTH(Table1[[#All],[Date]])=MONTH(B2)),--(YEAR(Table1[[#All],[Date]])=YEAR(B2)),(Table1[[#All],[Total]]))
=SUMPRODUCT((Table1[[#All],[Date issue]]=A2)*(MONTH(Table1[[#All],[Date]])=MONTH(B2))*(YEAR(Table1[[#All],[Date]])=YEAR(B2))*(Table1[[#All],[Total]]))

Anyone please help