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

#### tonywatsonhelp

##### Well-known Member
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

 Column D Column F 5 6 34 43 12 17 66 12 12 15 6 35 4 44 4 5 5 6 8 6 Total The blue ones would be the ones I want to add up

<tbody>
</tbody>

#### gaz_chops

##### Well-known Member
Maybe

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

Excel Workbook
DE
15
26
33443
412
5
61766
712
812
915
106
1135
12444
134
145
155
166
1786
18111171
Sheet1

#### tonywatsonhelp

##### Well-known Member
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

##### Well-known Member
That is what my suggestion does!

Code:
``````[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]``````

Last edited:

#### JoeMo

##### MrExcel MVP
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

##### Well-known Member
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

You're welcome.