looking for a short formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2491520%22%20slang%3D%22en-US%22%3Elooking%20for%20a%20short%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491520%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20several%20columns%20that%20need%20add%20then%20subtract%20then%20add%20then%20subtract.%20%26nbsp%3BFor%20example%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mangoorani_0-1624862130970.png%22%20style%3D%22width%3A%20638px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291864i430D5FC1265D7E09%2Fimage-dimensions%2F638x18%3Fv%3Dv2%22%20width%3D%22638%22%20height%3D%2218%22%20role%3D%22button%22%20title%3D%22Mangoorani_0-1624862130970.png%22%20alt%3D%22Mangoorani_0-1624862130970.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20short%20the%20formula%3F%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EM.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2491520%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-2491589%22%20slang%3D%22en-US%22%3ERe%3A%20looking%20for%20a%20short%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491589%22%20slang%3D%22en-US%22%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089617%22%20target%3D%22_blank%22%3E%40Mangoorani%3C%2FA%3E%20%2C%3CBR%20%2F%3E%3CBR%20%2F%3EWhy%20should%20it%20be%20possible%20to%20short%20the%20formula%20if%20you%20always%20go%20%2Ba-b-c%2Bd%2Be%2Bf%2Bg%20%3ADD%3CBR%20%2F%3EThere%20is%20no%20pattern%2C%20how%20you%20could%20short%20it.%3CBR%20%2F%3E%3CBR%20%2F%3ESchnittlauch%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2491597%22%20slang%3D%22en-US%22%3ERe%3A%20looking%20for%20a%20short%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491597%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089617%22%20target%3D%22_blank%22%3E%40Mangoorani%3C%2FA%3E%26nbsp%3BNot%20sure%20if%20your%20example%20is%20realistic%2C%20but%20if%20it%20is%20you%20could%20perhaps%20consider%20entering%20all%20the%20numbers%20that%20should%20be%20subtracted%20as%20negative%20numbers.%20Then%20the%20formula%20in%20your%20example%20could%20become%26nbsp%3B%3CSTRONG%3E%3D%24B4%20-%20SUM(%24D4%3A%24AB4)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2491675%22%20slang%3D%22de-DE%22%3ESubject%3A%20looking%20for%20a%20short%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491675%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089617%22%20target%3D%22_blank%22%3E%40Mangoorani%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20would%20be%20the%20shortest%20option%20because%20your%20data%20is%20uninterrupted%20from%20B4%20to%20AB4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20%3DSUM(B4%3AAB4)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2491728%22%20slang%3D%22en-US%22%3ERe%3A%20looking%20for%20a%20short%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089617%22%20target%3D%22_blank%22%3E%40Mangoorani%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DB4%2BSUMPRODUCT(D4%3AX4*(ISODD(COLUMN(D4%3AX4))*2-1))%2BAA4-Z4-AB4%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2495134%22%20slang%3D%22en-US%22%3ERe%3A%20looking%20for%20a%20short%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2495134%22%20slang%3D%22en-US%22%3EEvery%20other%20column%20is%20add%20then%20subtract%20from%20B4-AB4%2C%20so%20the%20sum%20formula%20won't%20work.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

I have several columns that need add then subtract then add then subtract.  For example, 

Mangoorani_0-1624862130970.png

Is there a way to short the formula?

Thanks,

M.

10 Replies
Hi @Mangoorani ,

Why should it be possible to short the formula if you always go +a-b-c+d+e+f+g :DD
There is no pattern, how you could short it.

Schnittlauch

@Mangoorani Not sure if your example is realistic, but if it is you could perhaps consider entering all the numbers that should be subtracted as negative numbers. Then the formula in your example could become =$B4 - SUM($D4:$AB4)

@Mangoorani 

That would be the shortest option because your data is uninterrupted from B4 to AB4.

 

Formula =SUM(B4:AB4)

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

@Mangoorani 

As variant

=B4+SUMPRODUCT(D4:X4*(ISODD(COLUMN(D4:X4))*2-1))+AA4-Z4-AB4
Every other column is add then subtract from B4-AB4, so the sum formula won't work.
Every other column is add then subtract from B4-AB4, so the sum formula won't work.

@Mangoorani 

 

Here is the screenshot.

@Mangoorani Perhaps you missed a few words in my earlier reply, where I suggested that you could perhaps enter the numbers  that need to be subtracted as negative numbers. Then you can use the SUM function as demonstrated in the picture below.

Screenshot 2021-06-29 at 08.20.53.png

I believe you could also try (edited after I saw you had uploaded a file):
=$B4-SUMPRODUCT(-1^ISERROR(SEARCH("Restock",$D3:$AB3)),$D4:$AB4)

@Mangoorani 

You wanted the shortest formula possible to perform the calculation you sent.

You got that from most of them, including me.

Send a file (without sensitive data) and use it to describe what exactly you want to accomplish.

Otherwise there is no point in smelling our fingernails.

 

I would recommend that you read this link carefully, as I recommend to most of you. This will save you a lot of time for yourself and for the helpers. Time that others might have at their disposal.

Willkommen in Ihrem Excel-Diskussionsraum!

 

I hope you also enjoy Excel.

I would be happy to see you ask here again.

Ask, ask as much as you like, just please with the given rules.

 

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)