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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,388
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 DColumn F
5
6
3443
12
1766

12
12
15
6
35
444
4
5
5
6
86
TotalThe blue ones would be the ones I want to add up

<tbody>
</tbody>
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,388
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
Joined
Apr 29, 2003
Messages
5,890
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:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,388
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
 

Forum statistics

Threads
1,085,879
Messages
5,386,521
Members
402,003
Latest member
johnpearson67

Some videos you may like

This Week's Hot Topics

Top