+ Reply to Thread
Results 1 to 5 of 5

Count Blank Cells which Today falls within a Date Range based on Adjacent cell

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2011
    Posts
    4

    Exclamation Count Blank Cells which Today falls within a Date Range based on Adjacent cell

    I've been trying to figure out this formula out for weeks but I can't find a solution in the forums that cover what I need!

    I need to count the number of blank cells in B1:B170 if the date in A1:A170 is +/- 3 weeks from today(). I need to count the number of cells that are empty only if there is a date listed in Column A as new rows will be added by a few different users on a daily bases. In a dream world, the cells which meet this criteria would be highlighted (I assume this would be easy to accomplish using conditional formatting once the right cells are identified?)

    (I'm tracking pregnant women who enroll in a program and need to have a quick way of seeing how many patients are full term (+/- 3 weeks from due date) and have yet to deliver. As patients enroll, I will add their expected due date in Column A, and will update Column B when they deliver.)

    Hope that is clear enough. Please let me know if you need any clarification or would like a sample set of data. I'd say my excel wrangling skills are moderately intermediate but I am totally stuck here.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Count Blank Cells which Today falls within a Date Range based on Adjacent cell

    Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    09-11-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Count Blank Cells which Today falls within a Date Range based on Adjacent cell

    Here you go:
    Example.xlsx

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Count Blank Cells which Today falls within a Date Range based on Adjacent cell

    In H19 Cell

    =COUNTIFS(G2:G13,"<>"&"",H2:H13,">="&TODAY(),H2:H13,"<="&SUM(TODAY(),21,(7-WEEKDAY(TODAY(),2))))

    In I19 Cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-11-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Count Blank Cells which Today falls within a Date Range based on Adjacent cell

    That is so close! Works perfectly when I enter it into the sample worksheet but when I enter it into my actual one, it returns the total number of patients with a due date within the right date range, including the patients that have already delivered. The sample worksheet includes a patient that has a due date within range and has already delivered (Patient G), so I'm not sure why it's giving me something different

    I reattached my sample worksheet with a more accurate representation, yet completely desensitized, data on my actual worksheet. (ID numbers where randomly generated by excel and totally arbitrary and dates changed)

    Example.xlsx

+ 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. Count if value matches AND falls within a date range
    By Tnim in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2013, 07:40 PM
  2. Replies: 4
    Last Post: 02-13-2013, 01:18 PM
  3. [SOLVED] AVERAGEIF if an adjacent cell falls within a certain RANGE
    By jesilva82 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2013, 11:52 AM
  4. Replies: 28
    Last Post: 04-21-2010, 11:45 PM
  5. [SOLVED] how to count if the value falls between a date range
    By joe in forum Excel General
    Replies: 3
    Last Post: 09-28-2005, 12:05 AM

Tags for this Thread

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