SOLVED
Home

SUM formula to accommodate monthly cadence, overlap, and match several attributes?

%3CLINGO-SUB%20id%3D%22lingo-sub-807401%22%20slang%3D%22en-US%22%3ESUM%20formula%20to%20accommodate%20monthly%20cadence%2C%20overlap%2C%20and%20match%20several%20attributes%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807401%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20really%20appreciate%20the%20help%20in%20creating%20a%20SUM%20formula%20to%20accommodate%20the%20monthly%20cadence%20of%20the%20%23%20of%20units%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EUnits%20are%20grouped%20yearly%20but%20are%20summed%20every%206%20months%3C%2FLI%3E%3CLI%3ESummed%20units%20are%20stored%20in%20a%20separate%20sheet%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20problem%20is%20the%20transition%20between%20the%20previous%20year%20to%20the%20next%20year%20(see%20yellow%2Forange%20highlight%20below).%20There's%20some%20overlap%20and%20is%20usually%20the%20current%20month%20and%20the%20previous%20month%20but%20in%20different%20row%20and%20column.%20The%20overlap%20needs%20to%20be%20added%20to%20the%20sum%20of%20the%20next%206%20months.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20create%20a%20formula%20that%20can%20accommodate%20these%20factors%20plus%20doing%20the%20calculation%20in%20a%20separate%20sheet%20using%20some%20type%20of%20reference%20to%20match%20which%20unit%20type%20and%20months%20to%20sum%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20everyone!%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%20style%3D%22width%3A%201003px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127187iD719393F81D4E337%2Fimage-dimensions%2F1003x345%3Fv%3D1.0%22%20width%3D%221003%22%20height%3D%22345%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-807401%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-807428%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20formula%20to%20accommodate%20monthly%20cadence%2C%20overlap%2C%20and%20match%20several%20attributes%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393045%22%20target%3D%22_blank%22%3E%40hashepsit%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20375px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127197iF8F1082DE232030F%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%20B2%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT(Cadence_Data!%24B%242%3A%24Y%245*(Cadence_Data!%24A%242%3A%24A%245%3D%24A2)*(Cadence_Data!%24B%241%3A%24Y%241%26gt%3B%3DB%241)*(Cadence_Data!%24B%241%3A%24Y%241%3CEDATE%3E%3C%2FEDATE%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20in%20second%20sheet%20attached.%20You%20only%20need%20to%20adjust%20the%20formula%20on%20your%20actual%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E
hashepsit
Occasional Visitor

Hello Excel Community,

 

I would really appreciate the help in creating a SUM formula to accommodate the monthly cadence of the # of units below.

 

  • Units are grouped yearly but are summed every 6 months
  • Summed units are stored in a separate sheet

The problem is the transition between the previous year to the next year (see yellow/orange highlight below). There's some overlap and is usually the current month and the previous month but in different row and column. The overlap needs to be added to the sum of the next 6 months. 

 

Is it possible to create a formula that can accommodate these factors plus doing the calculation in a separate sheet using some type of reference to match which unit type and months to sum? 

 

Thank you everyone!

 

clipboard_image_0.png

1 Reply
Solution

@hashepsit 

For such result

image.png

formula in B2 could be

=SUMPRODUCT(Cadence_Data!$B$2:$Y$5*(Cadence_Data!$A$2:$A$5=$A2)*(Cadence_Data!$B$1:$Y$1>=B$1)*(Cadence_Data!$B$1:$Y$1<EDATE(B$1,6)))

Please see in second sheet attached. You only need to adjust the formula on your actual ranges.

Related Conversations