+ Reply to Thread
Results 1 to 7 of 7

Countifs to count rows with a date which match a range of dates

  1. #1
    Registered User
    Join Date
    09-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Countifs to count rows with a date which match a range of dates

    Ok, been messing around with this, but not getting anywhere, and issue for me is that the criteria that has to be met, is actually a range of dates, that are not consecutive.

    Basically, patient database is in Sheet 1. Sheet 3 is where I want counts of how many patients meet certain criteria. First example which I have working is how many patients are from Spoke 100, Spoke 200, 300,etc. That was easy enough, just used formula - =COUNTIF(Sheet1!$A$1:$A$6716, 100) for counting the amount of patients from Spoke 100.

    But next thing is more complicated. I need to count how many patients have their date in Column E, matching a certain range of dates.

    For example,
    Starting with spoke 100 Week 1 Monday, How do I put a formula to count how many patients have "100" in Column A, but also have a date in Column E which matches any date which is in the range for Week 1 Monday (I.e. C11 to Z11)?

    May also need one more formula which is even more complicated. Same as above, count patients with 100 in column A, date in Column E in the range C11 to Z11,but will minus off any of those patients where the patients name is in a Red colour cell?

    thanks in advance
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Countifs to count rows with a date which match a range of dates

    any ideas? Does not necessarily need to use the COUNTIFS function, just thought that would be best way.

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Countifs to count rows with a date which match a range of dates

    Hi,

    Try this in C40 and copy to other relevant cells in rows 40-44:

    =SUMPRODUCT((Sheet1!$A$4:$A$217=100)*(ISNUMBER(MATCH(Sheet1!$E$4:$E$217,INDEX(Sheet3!$B$11:$Z$36,MATCH(Sheet3!B40,Sheet3!$B$11:$B$36,0),),0))))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    09-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Countifs to count rows with a date which match a range of dates

    Tried that, but every relevant cell in rows 40-44 just gives the answer 0?

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Countifs to count rows with a date which match a range of dates

    Really? See attached.

    Your first row (40) I agree, but that's only because there are no matches for Spoke 1 Monday, either for Week 1 Monday, Week 2 Monday, Week 3 Mon or Week 4 Mon - have you manually verified this?

    Copying the formula down, you will see that you get positive results for:

    Week 2 Tuesday, Week 3 Thurs, Week 1 Friday, Week 3 Fri and Week 4 Fri

    Regards
    Attached Files Attached Files
    Last edited by XOR LX; 09-07-2013 at 05:49 AM.

  6. #6
    Registered User
    Join Date
    09-16-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Countifs to count rows with a date which match a range of dates

    Ah right, my mistake, its works great thanks! Only other thing was is there a way to exclude patients where the cell in Column A is a certain colour? e.g. blue.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Countifs to count rows with a date which match a range of dates

    If the colouring is the result of some Conditional Formatting, or of some descriptive rule that we could add as a criterion, then this would be possible using formulas. If not, however, you'll require a VBA-based solution to achieve that, with which, I'm afraid, I won't be able to help you.

    Glad I could help on the other part, though, and best of luck.

+ 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. Replies: 3
    Last Post: 07-19-2010, 01:02 PM
  2. Replies: 8
    Last Post: 02-27-2009, 06:06 PM
  3. Count rows if date is between two specified dates
    By mg_sv_r in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2005, 09:25 AM
  4. Replies: 25
    Last Post: 09-07-2005, 12:05 AM
  5. [SOLVED] count date occurances in range of dates...
    By Alex in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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