Countif 2 cells match and another column is blank using dates.

Hey All,

I posted last week with the same spreadsheet and was thinking after I explained what I was looking for that it seemed too easy of an explanation and that was because I was not thinking it through.
I've attached a spreadsheet and here is what I really need.

On the Sheet1 sheet in cell H20 I am looking for the total number of blank spaces on the sheet renewal chart that are under the may section (lease expirations in column E of 5/31/2016). I only want the total for the same type though. So the Type in column B on the renewal chart should match the unit type on Sheet 1 column B.

The second part of this (which may require a different post) is on Sheet 1 cell L20 I want to do the same thing but instead of dates for this one it would need to look at the section labeled MTM on the Renewal Chart Sheet.

In this example both H20 and L20 would return a value of 1.

I probably should note that each month we remove the current month and add the next month. The Renewal Chart should never exceed 200 rows at anytime though.

Let me know if any part of my explanation doesn't make sense. I greatly appreciate any help on this.

Thanks!

Try

=COUNTIFS('Renewal Chart'!M:M,"",'Renewal Chart'!B:B,B20,'Renewal Chart'!E:E,EOMONTH(H$17,0))

That works great for the first Part (Cell H20). Thanks a ton!

Now for the second part? Since dates are not involved I would think this should be easier. Can I do a VLookup to match a blank cell?

I would think

=COUNTIFS('Renewal Chart'!M:M,"",'Renewal Chart'!B:B,B20,'Renewal Chart'!E:E,"MTM")

I guess this was easier then I though!


Thanks so much!

You're welcome.

So I found an error in my own logic on this. I can start a new thread if need be but didn't want to have to reattached. Here is my dilema:

In the MTM section (L20) in this example I would like it to count any one with a May or June expiration that has MTM listed in the Renewal Status column (column M) as well as any in the MTM section that is blank.

The May or June Expirations would have to somehow refer to cells H17 and J17 as those will change every month.

In this example it will return 0 unless you put MTM in column M for that floor plan on the Renewal Chart.

Let me know if more explanation is needed.

Thanks!

You could make 2 countifs and add them together

=COUNTIFS('Renewal Chart'!M:M,"",'Renewal Chart'!B:B,B20,'Renewal Chart'!E:E,"MTM")+COUNTIFS('Renewal Chart'!M:M,"MTM",'Renewal Chart'!B:B,B20)

The only issue with that is it counts anyone that has MTM in Column M will be counted and I really only want to count them if they are in the months that are in cells H17 and J17

Try adding it with the formula from post #2, changing the "" to "MTM"

So this is what I came up with that seems to work. I'm sure there is a much more efficient way to do it but seems to work nonetheless:

Formula:

Please Login or Register  to view this content.

I needed the MTM section to have the status as blank and the May and June to show MTM

Thanks so much for your help!

You're welcome.