subtracting values based on time remaining

[andrewb90]

Hello all,

I feel like I'm just slightly off on this formula: I am trying to sum a range of values, then subtract the total of all values where the time remaining is less that 1 year.

=(SUMIF(K3:K22,"<>"&"",D3:D22))-(SUMIF(K3:K22,[COLOR=#b22222]"<365"[/COLOR] &"",D3:D22))

=(SUMIF(K3:K22,"<>"&"",D3:D22))-(SUMIF(K3:K22,"GONE!" &"",D3:D22))

The second formula works perfect for removing data where the cell in Column K reflects Gone since there are no more days remaining.

 

[Vbagreenhorn1]

What is the problem and what is the purpose of the &"" in the formula?

What is in column K and how are the values getting in that column?

 

[andrewb90]

Here is the formula in K:

=IF(B3>F3,"GONE!",INT((F3-B3)/365)&"Y  "&INT(MOD((F3-B3),365)/30)&"M  "&ROUND(MOD((MOD((F3-B3),365)),30),0)&"D")

F3 & B3 are simple date values:ex 12/03/17