SUMIF cell has positive number, IF cell contains '0' return result of another cell

1
votes

I have a Google Sheets that is calculating date values. I would like to enter a formula that decreases the date pulled from a given cell if the cell contains a positive number. However, if the cell contains a zero, I would like it to simply return the value of a cell. Example:

Col A:
A1: 01/01/2018  
A2: 01/01/2018  
A3: 01/01/2018  

Col B:
B1: 0
B2: 1
B3: 2

Col C:
C1: Return Value of A1 (01/01/2018)
C2: Return Value SUM(A2+B2) (01/02/2018)
C3: Return Value SUM(A3+B3) (01/03/2018)

2
dategoogle-sheetsgoogle-sheets-formulaarray-formulas

2 Answers

0
votes

Please try automatically updated formula:

=ARRAYFORMULA(A1:A10+B1:B10)

enter image description here

Notes:

  • change ranges to yours
  • you may modify the formula to get dynamic ranges: =filter(A:A+B:B,A:A>0)
0
votes

This seems relatively straight forward based on your example? See this google sheet... you just add the date to column b? get the outcome you specified? I'm not sure why you should need an Array as Max has done, but this seems to give the same result: =B1+A1

I did notice that you mentioned DECREASE the date. In that case, wouldn't you just subtract the date based on an if statement? I did that in row 20 with this formula: =if(B20>0,A20-B20,A20)

enter image description here