Formula to add values from two columns ingoring one if the other has an amount in it?

[tonywatsonhelp]

Hi Everyone,

I Have two ranges

D20:D250 & F20:F250

I need a total these with this rule

Not all cells have a value,
Column D needs to be ignored if column F has an amount
example

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]The blue ones would be the ones I want to add up[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Maybe

=SUMIFS(D20:D250,F20:F250,"")+SUM(F20:F250)

 

[JoeMo]

Excel Workbook
	D	E
1	5
2	6
3	34	43
4	12
5
6	17	66
7		12
8	12
9	15
10	6
11	35
12	4	44
13	4
14	5
15	5
16	6
17	8	6
18	111	171
Sheet1

 

[tonywatsonhelp]

Hi Everyone,
Maybe I didn't make it clear I only want 1 total.
So if E has no value use D if it does use E

 

[gaz_chops]

That is what my suggestion does!

[TABLE="width: 261"]
<tbody>[TR]
[TD="class: xl64, width: 87, align: right"]5[/TD]
[TD="class: xl64, width: 87"][/TD]
[TD="class: xl64, width: 87"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]34[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"]43[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]17[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"]66[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]15[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]35[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"]44[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl64, align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl65, align: right"]282[/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Hi Everyone,
Maybe I didn't make it clear I only want 1 total.
So if E has no value use D if it does use E

Doesn't the solution in post #2 do that?

 

[tonywatsonhelp]

Hi Gaz Chops,
Sorry my mistake, I changed the ranges slightly and get y formula wrong
Yours worked perfect.
Thank you very much for your help
Tony

 

[gaz_chops]

You're welcome.