Conditional Formula Assistance Needed for Property Mgt Spreadsheet

 Hello - I am working on a Property Management spreadsheet to track the availability of vacant units (and upcoming vacant units).  I'm trying to write a formula to count the number of current units that are listed as vacant, so that I can ultimately report on the overall availability as of any given period (today, 15-day, 30-day, 60-day, 90-days out).

Here are the fields that I am using for my analysis:

Column B = "Status".  Values are set as either "V" vacant, or "O" occupied.  The range = B3:B84

Column J = M/O Date.  These dates represent when the current resident will move out.

Column R = M/I Date.  These dates represent when the future resident will move in.

Column S = Reporting Period Date.  These dates represent the availability for a given period of time.  For example, S8 = TODAY (), S9 = 15-Day (=TODAY+15), S10 = 30-Day (=TODAY=15), etc.

I need my formula to look for all of the vacants ("V") in column B.  This is what I used so far: =COUNTIF(B4:B393,"=V").  This is working fine, however, I ALSO need for my formula to take into account and remove any units where a new resident is moving in during the reporting period.  So it will need to look at column R and remove anything with a date <= the date listed in Column S.

Here is an example:  Lets say as of today we have 20 vacant units (from B3:B84).  For my availability report I need to calculate the vacants as of today, 15 days from now, etc.  So lets say there will be 7 new residents moving in from now until 5/20/08.  I need to revove those 7 from my total.  So the formula should calculate 20-7=13.

Is there a way to do this with one formula, or do I need multiple formulas to accomplish this???  Hopefully this isn't too confusing.

I appreciate any help any of you can provide.  Thank you!

I think I understand, you can use a SUMPRODUCT for multi condition counting or summing, like so

=SUMPRODUCT(--(B3:B84="V"))-SUMPRODUCT(--(R3:R84<=S3:S84))

Thank you very much for the quick reply.  This appears to be working and doing what I want it to do.  However, the value is coming back negative.  Is there a way to change the formula so that the returned value is not negative?

Also, my spreadsheet will potentially contain data up through line 393, so my range for Column B and R will actually be B3:B393.  Currently there isn't anything there, but I need to include these cells in the range, but don't want it to factor in any blank cells.  Does that make sense?  Please help.

Does this do it?

=COUNTIF(B3:B393,"V")-SUMPRODUCT(--(B3:B393="V"),--(R3:R393<=S3:S393),--(ISNUMBER(R3:R393)),--(ISNUMBER(S3:S393)))

 Thank you for your help.  Unfortunately, I am still getting an error when applying this formula.  The cell value is showing #VALUE!.  I'm not sure why this isn't working.  

PS - will blank cells affect the outcome of this formula?  My spreadsheet has 393 rows, but most of them are blank.  I need to ensure that this isn't factoring in blank cells if that's possible.

It shouldn't matter. I put the ISNUMBER tests in there to allow for blank dates, and a blank status is being checked specifically for V anyway.

I just plugged it into a completetly empty spreadsheet, and it returned 0 very nicely.

Siunds like you have some data problems to me.

 Hmmm, I created a new test spreadsheet and I do get a value of "0" when I paste this formula in.  However, as soon as I add any real data I am now getting an error #NAME?

Here is basically what I have in my test spreadsheet:

Column A - (Reporting Period Dates):  A2 = 5/20/08, A3 = 6/4/08, A4 = 6/19/08, A5 = 7/4/08

Column B - (Unit Status): B2 = V, B3 = V, B4 = V, B5 = O

Column C - (Move In Date): B2 = 5/15/08, B3 = 5/17/08, B4= 5/25/08, B5 = 6/1/08

The formula should be searching for all of the V's, so in this scenario there are 3 out of the 4 units that are vacant.

For the first reporting period (A2), I am interested in knowing how many vacant units there will be as of 5/20/08, so any V's <= 5/20/08 should be removed from the total.  In this case we have 3 V's, two of which will be rented prior to 5/20/08 so they fall off.  The number I'm ultimately looking for would be 1, since I would still have the one remaining unit not renting until 5/25.

Hope this makes sense.  I'm a not an Excel guru, so I really appreciate your help!

Steve

I am getting confused by your data. Initially you said thta the data started in row 3. Now you say row 2. Okay, so I assumed that the values in row 2 were te values to test against, but if so, that would mean there are only 2 Vs, but you say there are 3.

Hey, have I seen the light?

=N(COUNTIF($B$2:$B$39,B2)-SUMPRODUCT(--($B$2:$B$39=B2),--($C$2:$C$39<=A2),--(ISNUMBER($C$2:$C$39))))

Ok, I'm getting closer now.  This new formula did return a valid value, but it doesn't appear to be calculating correctly. 

Sorry for the confusionon this.  I had created a test spreadsheet with a sub-set of my original data.  For purposes of the test spreadsheet don't go off of my original post.  I only have data in three columns for the test spreadsheet:

Column A - (Reporting Period Dates):  A2 = 5/20/08

Column B - (Unit Status): B2 = V, B3 = V, B4 = V, B5 = O

Column C - (Move In Date): B2 = 5/15/08, B3 = 5/17/08, B4= 5/25/08, B5 = 6/1/08

If my reporting period is 5/20/08 (cell A2), then I need to calculate how many Vacant units I will have as of 5/20.   In this case I have three V's in column B.  Column C represents the dates that the new tenants will move into these vacant units, so the formula should not count B2 and B3 since they both fall before 5/20/08.  This would leave me with only 1 vacant unit for the reporting period.

When I apply your new formula, the number goes up if I change the values in column B from V to O.  This definitely should not be counting any O's (occupied).  The values in column B are dynamic and can change at any time from V to O, so the formula should only be searching for the one's that have a V in Column C.

I'm sorry I have made this so confusing.  Thanks again for the help.

S

I don't see that effect. If I change B4 to V, the count goes down to 0, but that is to be expected as row 4 is not vacant. If I change B3 to O, nothing changes, which as it is not vacant, is what we would expect.

Thanks for sticking with me on this Bob.  I appreciate your help.   I think your formula is working correctly, however, you have this set to count whatever the value is in cell B2 (=N(COUNTIF($B$2:$B$39,B2).......), so if I change the unit status in cell B2 to '"O" that completely changes the count since it is now calculating the occupieds.  Can you adjust this formula so that it will ONLY look for V's?  Or could we just use cell B2 as a set value (never to change) and exclude this one from the count total?  I hope I am making sense.

It checks it in two places, one where it counts them, one where it reduces the occupied. So we change it to

=N(COUNTIF($B$2:$B$39,"V")-SUMPRODUCT(--($B$2:$B$39="V"),--($C$2:$C$39<=A2),--(ISNUMBER($C$2:$C$39))))