asked on # Help required on amending a excel formula

I have this formula in a workbook of which looks at data in Column E but im trying to get it to do the same but when data is put into column E OR F .

Any Ideas?

=IF(A11>0,E11-SUM(H11:V11),"")

Microsoft ExcelMicrosoft Office

Sorry im confused there but I think I may of mislead you. The formula is shown in column W and im trying to get it to work when data put into column E or F and calculate it in W

If Sam didn't give you an answer that works, you might want to provide an example of the data.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

Perhaps something like this?

IF(A11>0,IF(ISBLANK(E11),F11-SUM(H11:V11),E11-SUM(H11:V11)))

IF(A11>0,IF(ISBLANK(E11),F11-SUM(H11:V11),E11-SUM(H11:V11)))

Enclosed is part of the workbook that Im using.

If you put a date in column A and a cash figure in column E it copies the cash figure to column W.

Then as you input data in columns H-V it reduces the figure in column W.

I need it to do the same principal but be able to put a cash figure in column E or F and then input the data into H-V etc.

tuesday.xlsb

If you put a date in column A and a cash figure in column E it copies the cash figure to column W.

Then as you input data in columns H-V it reduces the figure in column W.

I need it to do the same principal but be able to put a cash figure in column E or F and then input the data into H-V etc.

tuesday.xlsb

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a questionGet an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

I probably should have posted a sample file, because your list separator is different from mine.

tuesday.xlsb

tuesday.xlsb

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

=IF(B11>0,F11-SUM(I11:W11),"").

If you want any references to be absolute, you would preference the row or column (or both) with a dollar sign. For example, if you always wanted to sum H11-V11 regardless of the row or column the formula was in, you would use:

=IF(A11>0,E11-SUM($H$11:$V$11),"").

Now if you copy the formula to column F, it would look like:

=IF(B11>0,F11-SUM($H$11:$V$11),"").

Only the row/column references without $ will be adjusted.