Sum by Month

%3CLINGO-SUB%20id%3D%22lingo-sub-1710515%22%20slang%3D%22en-US%22%3ESum%20by%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1710515%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20Sum%20by%20month%20but%20no%20luck.%20Please%20see%20my%20attached%20excel%20data.%20The%20first%20tab%20has%20datewise%20data%20and%202nd%20tab%20is%20for%20monthly%20sum%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ITlover2350_0-1600978895638.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F221922i538C89FC68C4B105%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22ITlover2350_0-1600978895638.png%22%20alt%3D%22ITlover2350_0-1600978895638.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ITlover2350_1-1600978922148.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F221923i5F33022E368B773D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22ITlover2350_1-1600978922148.png%22%20alt%3D%22ITlover2350_1-1600978922148.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20help%20me%20with%20...%20put%20the%20formula%20in%20my%20attached%20file.%20Thanks%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1710515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1710543%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20by%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1710543%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F808107%22%20target%3D%22_blank%22%3E%40ITlover2350%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPivotTable%20for%20such%20aggregations%20is%20better%2C%20but%20as%20variant%20you%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((Data!%24B%242%3A%24B%2414)*(TEXT(Data!%24A%242%3A%24A%2414%2C%22mmmm%22)%3DSum!A13))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ehere%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20364px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F221925i6AFF73CA18A2D029%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1710598%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20by%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1710598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20quick%20help!.%20Can%20you%20please%20attach%20a%20solution%20excel%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I am trying Sum by month but no luck. Please see my attached excel data. The first tab has datewise data and 2nd tab is for monthly sum calculations.

 

ITlover2350_0-1600978895638.png

ITlover2350_1-1600978922148.png

 

Can you please help me with ... put the formula in my attached file. Thanks much!

 

5 Replies

@sbhan4 

PivotTable for such aggregations is better, but as variant you may use

=SUMPRODUCT((Data!$B$2:$B$14)*(TEXT(Data!$A$2:$A$14,"mmmm")=Sum!A13))

here

image.png

@Sergei Baklan 

 

Thank you for your quick help!. Can you please attach a solution excel sheet.

@sbhan4 

It is attached to my previous post, please check at bottom.

@Sergei Baklan . Thanks. How can i do the Sum by Month and Year? For example if there is data for multiple year and i need data for particular year what i need to do?

@sbhan4 

In general it's better to use PivotTable with grouping dates on months and years. 

You may use SUMPRODUCT or SUMIFS as well with MONTH() and YEAR() inside to filter periods. 

 

It depends on your source data structuring and in which form you'd like to have the result.