+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    133

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

    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!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

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

    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!
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Contributor
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    133

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

    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!

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

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

    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=""))

  5. #5
    Forum Contributor
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    133

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

    Awesome. Works like a charm! Thanks!

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Blanks cells against date picking row to column without duplication
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-24-2015, 04:52 AM
  2. [SOLVED] Blanks cells against date picking row to column without duplication
    By Vandini.S in forum Excel General
    Replies: 12
    Last Post: 08-20-2015, 02:17 AM
  3. Blanks cells against date picking without duplication in row to column
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-26-2015, 08:40 AM
  4. Replies: 3
    Last Post: 05-09-2015, 01:25 AM
  5. [SOLVED] VBA Macro - Insert Rows based on different Column Value but Ignoring Blanks Cells
    By WITJ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-12-2013, 03:11 PM
  6. Count column w/blanks & values based on specific date
    By nickelcell1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-30-2009, 04:08 PM
  7. Subtract a group of cells from a total based on ending date
    By Nicholas Scarpinato in forum Excel General
    Replies: 0
    Last Post: 05-17-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1