# looking for a short formula

I have several columns that need add then subtract then add then subtract.  For example, Is there a way to short the formula?

Thanks,

M.

10 Replies

# Re: looking for a short formula

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

# Re: looking for a short formula

@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)

# Betreff: looking for a short formula

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.

# Re: looking for a short formula

As variant

``=B4+SUMPRODUCT(D4:X4*(ISODD(COLUMN(D4:X4))*2-1))+AA4-Z4-AB4``

# Re: looking for a short formula

Every other column is add then subtract from B4-AB4, so the sum formula won't work.

# Betreff: looking for a short formula

Every other column is add then subtract from B4-AB4, so the sum formula won't work.

# Re: looking for a short formula

Here is the screenshot.

# Re: looking for a short formula

@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. # Re: looking for a short formula

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)

# Betreff: looking for a short formula

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.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)