SOLVED

SUM by Month

%3CLINGO-SUB%20id%3D%22lingo-sub-731429%22%20slang%3D%22en-US%22%3ESUM%20by%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731429%22%20slang%3D%22en-US%22%3E%3CP%3EIm%20trying%20to%20Sum%20by%20Month%3C%2FP%3E%3CP%3EThe%20data%20that%20is%20referenced%20is%20on%20a%20different%20worksheet%20in%20the%20same%20workbook.%3C%2FP%3E%3CP%3EThe%20table%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EMonth%3C%2FTD%3E%3CTD%3E2017%3C%2FTD%3E%3CTD%3E2018%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJanuary%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFebruary%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMarch%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EApril%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMay%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJune%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJuly%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAugust%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESeptember%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOctober%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ENovember%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDecember%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3CTD%3E%240.00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%20data%20is%20on%20a%20worksheet%20in%20same%20workbook%20called%20PC%3C%2FP%3E%3CP%3EThe%20date%20is%20in%20column%20AA%20of%20Worksheet%20PC%20and%20the%20title%20of%20the%20column%20is%20%22Close%20Date%22%3C%2FP%3E%3CP%3EAnd%20the%20data%20that%20would%20populate%20the%20monthly%20sum%20is%20in%20column%20AC%20of%20worksheet%20PC%26nbsp%3B%20and%20the%20title%20of%20the%20column%20is%20%22Profit%2FLoss%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-731429%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-732976%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20by%20Month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F240888%22%20target%3D%22_blank%22%3E%40Yehuda%20Ordower%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20380px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121812i0B378E2C94BBFC50%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20in%20C3%20is%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((YEAR(%24F%243%3A%24F%2447)%3DC%242)*(TEXT(%24F%243%3A%24F%2447%2C%22mmmm%22)%3D%24B3)*%24G%243%3A%24G%2447)%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20the%20right%20and%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20adjust%20that%20to%20your%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Im trying to Sum by Month

The data that is referenced is on a different worksheet in the same workbook.

The table looks like this:

Month20172018
January$0.00$0.00
February$0.00$0.00
March$0.00$0.00
April$0.00$0.00
May$0.00$0.00
June$0.00$0.00
July$0.00$0.00
August$0.00$0.00
September$0.00$0.00
October$0.00$0.00
November$0.00$0.00
December$0.00$0.00

The data is on a worksheet in same workbook called PC

The date is in column AA of Worksheet PC and the title of the column is "Close Date"

And the data that would populate the monthly sum is in column AC of worksheet PC  and the title of the column is "Profit/Loss"

 

Thanks,

 

 

1 Reply
Highlighted
Solution

@Yehuda Ordower 

For such sample

image.png

formula in C3 is

=SUMPRODUCT((YEAR($F$3:$F$47)=C$2)*(TEXT($F$3:$F$47,"mmmm")=$B3)*$G$3:$G$47)

drag it the right and down.

 

You may adjust that to your ranges.