#value error from edate function inside an if statment to get one of two numbers

A.png


The error is with using the EDATE function.


The goal is to get the correct total loss of income of a given month.

From the attached excel document...

Given a set of two numbers under columns titled "base rent" and "new rent", check to see if the month of the current date "TODAY()" IS >= a given date.

We do this to subtract the cost of all cells that contain the word "vacant" to adjust the expected income for a month.

• Attached is an excel document with the problem areas marked in red.
• Conditional formating was used to highlight cells that contain the word "vacant" black.

Below is the formula I'm trying to use to solve this problem.

Please Login or Register  to view this content.

Given the formula above, I'm understanding it as:

1. Get the sum of everything in a column
2. if a cell contains the word vacant then
3. if the current integer obtained from the (month) function that gets a date from a cell from B2:b13 with the date forward by 3 months due to EDATE(B2:b13,3) function, if the integer is greater then or equal to the value of the cell E19 (1, in this case) then subtract the value in the "new Rent" Column
4. Else subtract from the rent from the base rent column.

The correct expected total loss should be -3000$.

I even think this part is redundant:
=SUM(IF(ISNUMBER(SEARCH("vacant",E2:E11),(MONTH($C$2:$C$11)>=E18)*(-1*D2:D11)+(MONTH($C$2:$C$11)<=E18)*(-1*A2:A11))))