SOLVED
Home

Specific SUM formula

%3CLINGO-SUB%20id%3D%22lingo-sub-633255%22%20slang%3D%22en-US%22%3ESpecific%20SUM%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-633255%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20with%20a%20table%20consisting%20of%20three%20columns.%20One%20column%20has%20months%2C%20the%20other%20has%20revenues%20and%20in%20the%203rd%20column%20I%20need%20the%20sum%20of%20the%20last%2012%20months%20of%20revenue.%20This%20means%20that%20the%20SUM%20column%20will%20have%20its%20first%20entry%20on%20the%2012th%20row%2C%20when%2012%20months%20of%20revenue%20is%20available.%20So%20for%20example%20for%20rows%20Jan-%20Dec%202010%20There%20will%20be%20one%20entry%20in%20the%20SUM%20column%2C%20and%20on%20the%20next%20row%20the%20column%20will%20now%20display%20the%20summed%20revenues%20for%20Feb%202010-%20Jan%202011.%20I%20need%20this%20to%20keep%20updating%20in%20time%20as%20entries%20are%20made.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20this%20would%20be%20as%20simple%20as%20dragging%20down%20the%20first%20cell%20with%20this%20formula%2C%20but%20excel%20recognizes%20the%20sum%20formula%20itself%20as%20an%20error%20(for%20not%20including%20all%20nearby%20cells).%20I'm%20sure%20there%20is%20an%20easy%20solution%20but%20I%20am%20struggling%20to%20find%20it.%20Any%20ideas%3F%20Thanks!%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-633255%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-633334%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20SUM%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-633334%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313240%22%20target%3D%22_blank%22%3E%40dzhogov%3C%2FA%3E%26nbsp%3B%2C%20for%20the%20model%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20353px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F115662i3FDD9FB6974426BE%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%3Eyou%20may%20use%3C%2FP%3E%0A%3CPRE%3E%3DSUM(INDEX(B%3AB%2CROW()-MIN(12%2CROW()-ROW(%24A%241)%2B1)%2B1)%3AINDEX(B%3AB%2CROW()))%3C%2FPRE%3E%0A%3CP%3Ein%20C2%20and%20drag%20it%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-633379%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20SUM%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-633379%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%20and%20effective%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-633427%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20SUM%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-633427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313240%22%20target%3D%22_blank%22%3E%40dzhogov%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
dzhogov
Occasional Contributor

Hello all,

 

I am working with a table consisting of three columns. One column has months, the other has revenues and in the 3rd column I need the sum of the last 12 months of revenue. This means that the SUM column will have its first entry on the 12th row, when 12 months of revenue is available. So for example for rows Jan- Dec 2010 There will be one entry in the SUM column, and on the next row the column will now display the summed revenues for Feb 2010- Jan 2011. I need this to keep updating in time as entries are made.

 

I thought this would be as simple as dragging down the first cell with this formula, but excel recognizes the sum formula itself as an error (for not including all nearby cells). I'm sure there is an easy solution but I am struggling to find it. Any ideas? Thanks!

 

 

3 Replies
Solution

@dzhogov , for the model like this

image.png

you may use

=SUM(INDEX(B:B,ROW()-MIN(12,ROW()-ROW($A$1)+1)+1):INDEX(B:B,ROW()))

in C2 and drag it down.

 

@Sergei Baklan 

 

Thank you for your quick and effective reply.

@dzhogov , you are welcome