Total blanks in a set of cells based on the date in a different column

[heresteve2]

Sorry that my Title wasn't great but hard to explain in limited space.

I've attached the spreadsheet I'm looking at and will do my best describe what I'm attempting to do. I know the awesome community will once again bail me out!

I'm looking for a formula in cell H20 of Sheet 1 that will return the total number of cells in the sheet Renewal Chart that are blank under column M but only for those with lease Exp in May.

Actually thought I would have to do a lot more explaining, so hopefully that makes sense. In this example it should return the value 1.

Thanks in advance and let me know if more explanation is needed!

[loginjmor]

Hi -

To keep it simple, I added a helper column in your Renewal Chart sheet to give me the Month number or each date. You can hide that column if you would like. Then it's simply a matter of counting the number of cells in Column N (Used to be M before I inserted a helper column) that are blank AND have a date in the month of May (month 5). The formula looks like this:

=COUNTIFS('Renewal Chart'!$F$4:$F$43,MONTH(H$17),'Renewal Chart'!$N$4:$N$43,"")

Attached is a copy of your spreadsheet so you can see the helper column and the formula in H20.

Hope this helps!

[heresteve2]

Thanks for the help. While this ultimately gets the correct value is there a way around having that extra column? This is an ever evolving workbook and asking people to make sure that number is getting entered is more to keep track of and more areas to trouble shoot when something goes wrong. Thanks!

[loginjmor]

Hi -

Yes, but you have to do it as an array formula, and I'm going to switch to SUMPRODUCT since I struggle to get COUNTIFS to work with array formulas. Just copy and paste this formula into H20, and from the formula editor window press Ctrl-Shift-Enter at the same time. If you did it right, Excel will add curly braces {} around the formula.

=SUMPRODUCT((IFERROR(IF(ISBLANK('Renewal Chart'!$E$4:$E$38),0,MONTH('Renewal Chart'!$E$4:$E$38)),0)=MONTH(Sheet1!H$17))*('Renewal Chart'!$M$4:$M$38=""))

[heresteve2]

Awesome. Works like a charm! Thanks!

[loginjmor]

Great! Glad it is working. Thanks for the bump!