 # Sum By Condition & Other Tests

• 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.

## Files

• 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

But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

• 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

But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

• 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.