I am trying to use IF and SUM formula as below in cell K11.
=IF(F11=0,SUM($E$39+E11+K11-$F$39),SUM($E$39-I11+K11))
However I am not getting it right on the first part of the formula.
I need if cell F11 is empty, =cell E39 - (sum cell F39 with cell E11). Beginning from the point where column F is empty, the formula should add cell F39 with the cumulative column of column E.
Example is on cell H27, the formula should add cell F39 with the total of cell E19 to E27.
Appreciate help.
Re: If And Sum Formula
In H11, enter =SUM(($E$10:E10)*($F$10:F10=""),$F$10:F10) and press Ctrl+Shift+Enter.
Then drag down.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]An even better way would be to just put =IF(F10="",E10,F10)+H10 in H11 and drag that down.
Re: If And Sum Formula
hi kumara_faith,
looking at your original formula - it must be circular as cell K11 is trying to calculate a number from K11.
The post by darkyam is an array formula - very powerful and can cause greif if you are not farmiliar with them.
The sum formula you might try is
=IF(F11=0,SUM($E$39, E11, K11, -$F$39),SUM($E$39, -I11, K11)) and should be entered in another cell other then K11.
Hope this helps
GB
Re: If And Sum Formula
My apologies, the formula actually should be in K11.
There is two part to the formula as below:
F(F11=0,SUM($E$39+E11+J11-$F$39),SUM($E$39-H11+J11))
The second part in bold is working fine. What I need the first part is the first part to have cell E39 (4153) minus the sum of cell F36 plus cell E11.
For example, in cell K27, the formula result should be as follows:
=4153- (1410+1289)
=1454
The 1289 in the above is a combination from cell E19 to E27.
Is there a way to incorporate this criteria in the first part of the formula ? Thanks.
Re: If And Sum Formula
Try =IF(F11=0,$E$39-SUM(F$10:F11)-SUM((E$10:E11)*(F$10:F11="")),SUM($E$39-H11+J11)), hit Ctrl+Shift+Enter, and copy down.
Re: If And Sum Formula
Probably the best way to do this is by using a sumif formula such as
=E39-SUMIF(F10:F27,">1",E10:E27)-F39 (this is for cell k27)
What it does is
takes the total of 4153 (e39) less the sum of all numbers in column "e" only if there is nothing in column "f" less completed tasks sum at f39
Hope this helps
GB
Re: If And Sum Formula
Thank you Graham and Darkyam for your responses. Appreciate it !
If you wish to continue using this free service.
Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.
In future, please take 1 minute of your time to read the text on the New Thread page.
[fa]*[/fa]
REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.
Don’t have an account yet? Register yourself now and be a part of our community!
