+ Reply to Thread
Results 1 to 21 of 21

Count the number of times a date appears in a range

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Count the number of times a date appears in a range

    Hi,

    I would like to count the number of times a specific date appears in a range and only return a value if the result is not 0.

    Example data:

    A1 B1 C1 D1
    01/01/2013 05/01/2013 01/01/2013 02/01/2013

    So based on the above data, the formula would look at cell c1 and count how many times the date specified occurs between A1 and B1. Which would return a value of 1.

    I have tried the following: =IF(COUNTIFS($A$1:$B$1, ">="&C$1)=0,"",COUNTIFS($A$1:$B$1, ">="&C$1)) but the problem is it counts the date twice returning a value of 2. Removing the > symbol fixes it but then if asked to look at cell D1 it doesn't return a value.

    Could someone please help?

    Thanks in advance!

  2. #2
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Apologies, the example data hasn't came across properly. It's as follows:

    A1 = 01/01/2013
    B1 = 05/01/2013
    C1 = 01/01/2013
    D1 = 02/01/2013

  3. #3
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Count the number of times a date appears in a range

    Something like this?
    Attached Files Attached Files
    Please click 'Add reputation', if my answer helped you.

  4. #4
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Hi ramananhrm

    The date range to look in would always be between two columns. Columns A & B in this case

  5. #5
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Could anybody please help?

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count the number of times a date appears in a range

    Hi and welcome to the forum.

    Be sure that best way to describe your problem is to upload a sample workbook.

    Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

    To attach a small sample workbook.

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

    View Pic
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Count the number of times a date appears in a range

    welcome to the forum, uhtfgy. your profile shows MS-Off Ver of Excel 2003 but you suggested COUNTIFS. that is only available in Excel 2007 & above. do change your profile to help us help you better. if you didn't mention COUNTIFS, we would have assumed you couldn't use that.

    so C1 must be between A1 & B1 to show as 1, & 0 if that's not the case? why would there be a case where you need to count the number of times it appear? it can only appear once right? if that's correct, then try:
    =COUNTIFS(C$1,">="&$A$1,C$1,"<="&$B$1)

    otherwise, do let us know what scenario does it happen where more than 1 count appears

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  8. #8
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Hi benishiryo,

    Thank you.

    I have attached a sample workbook and amended my office version.

    Customer table = work planned
    Work load table = summary


    What I'm trying to do in the work load table is count how many customers we are working on each day by looking at the start and end days (and days in between) for all customers. I have included expected outcomes below the workload table.

    Thank you.
    Attached Files Attached Files

  9. #9
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Count the number of times a date appears in a range

    in H6:
    =COUNTIFS($B$2:$B$9,"<="&H5,$C$2:$C$9,">="&H5)

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count the number of times a date appears in a range

    Or this:

    =SUMPRODUCT(($B$2:$B$9<=H5)*(($C$2:$C$9>=H5)))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  11. #11
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Thank you so much - they both work!!

    You guys are superstars

    Could I get the same outcome by having the range in one cell e.g. 01/10/13 - 05/10/13 in the same cell instead of having the from and to dates in different cells?

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count the number of times a date appears in a range

    You're welcome and thank you for your feedback! Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  13. #13
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Count the number of times a date appears in a range

    Quote Originally Posted by uhtfgy View Post
    Could I get the same outcome by having the range in one cell e.g. 01/10/13 - 05/10/13 in the same cell instead of having the from and to dates in different cells?
    not that you can't, but putting Start Date & End Date in 2 cells would be more ideal. say 1Oct13 now in H5 & 5Oct13 in I5, then:
    =SUMPRODUCT(COUNTIFS($B$2:$B$9,"<="&ROW(INDIRECT(H5&":"&I5)),$C$2:$C$9,">="&ROW(INDIRECT(H5&":"&I5))))

  14. #14
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Quote Originally Posted by benishiryo View Post
    not that you can't, but putting Start Date & End Date in 2 cells would be more ideal. say 1Oct13 now in H5 & 5Oct13 in I5, then:
    =SUMPRODUCT(COUNTIFS($B$2:$B$9,"<="&ROW(INDIRECT(H5&":"&I5)),$C$2:$C$9,">="&ROW(INDIRECT(H5&":"&I5))))

    Hi Benishiryo,

    Thank you, but I'm not sure what's happening in that bit of code. I meant having the data set in the same field, so the dates in B2 and C2 would be in one cell instead of in two, and compare that to the date in H5.

    On a separate note, I have tried using both the countifs and sumproduct variations suggested above but set it to look at another sheet instead for the date range (columns B and C) and the original sheet for the date to check against (cell H5) but I can't seem to get it to work:

    =SUMPRODUCT(('Sheet2'!$B$2:$B$9<=H5)*(('Sheet2'!$C$2:$C$9>=H5)))

    =COUNTIFS('Sheet2'!$B$2:$B$9,"<="&H5,$C$2:$C$9,">="&H5)

    Any ideas please?

    Many Thanks

  15. #15
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Could anybody please help?

  16. #16
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count the number of times a date appears in a range

    If formulas provided to you do not work in your new setting, upload a spreadsheet with a formula to show what is not working. Without seeing your data set it would be rather difficult to tell what needs to be done.

  17. #17
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    I have attached a sample workbook - it's the same as before only the work load table is on a separate sheet.

    Customer table = work planned
    Work load table = summary


    What I'm trying to do in the work load table is count how many customers we are working on each day by looking at the start and end days (and days in between) for all customers. I have included expected outcomes below the workload table.

    Both

    =COUNTIFS($B$2:$B$9,"<="&H5,$C$2:$C$9,">="&H5)

    and

    =SUMPRODUCT(($B$2:$B$9<=H5)*(($C$2:$C$9>=H5)))

    work when the work load table is on the same sheet. How can I change the code to look in Sheet 1 instead for the customer dates?
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    12-06-2012
    Location
    sacramento,calif
    MS-Off Ver
    Excel 2010
    Posts
    217

    Re: Count the number of times a date appears in a range

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

  19. #19
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count the number of times a date appears in a range

    Here is also COUNTIFS with Sheet1 references.

    =COUNTIFS(Sheet1!$B$2:$B$9,"<="&H5,Sheet1!$C$2:$C$9,">="&H5)

  20. #20
    Registered User
    Join Date
    09-30-2013
    Location
    London
    MS-Off Ver
    365 ProPlus
    Posts
    12

    Re: Count the number of times a date appears in a range

    Thank you so much to both of you!!

    I've realised where I was going wrong, and I hate to admit my very stupid mistake, but I was referencing the wrong sheet!!!

    Thank you again!

  21. #21
    Registered User
    Join Date
    07-12-2015
    Location
    Philippines
    MS-Off Ver
    2007
    Posts
    1

    Cool Re: Count the number of times a date appears in a range

    [QUOTE=uhtfgy;3428999]Hi,

    I would like to count the number of times a specific date appears in a range and only return a value if the result is not 0.

    Example data:

    A1 B1 C1 D1
    01/01/2013 05/01/2013 01/01/2013 02/01/2013

    So based on the above data, the formula would look at cell c1 and count how many times the date specified occurs between A1 and B1. Which would return a value of 1.

    Try this instead:
    Let's say, go to cell A3 and type the date you would like to find, for example 01/01/2013.
    1. then go to A4 cell and type this: = countif(A1:D1, A3)
    and voila...it already counts the number of times the date from A3 appears from cells A1 to D1.

    I do hope this will help you.
    Last edited by Jekaw; 07-12-2015 at 05:58 AM.

+ 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 the number of times a number appears in a specific range
    By myjebay1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2013, 11:01 AM
  2. [SOLVED] Want to count the number of times a particular letter appears within a range
    By dwhite30518 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-11-2013, 01:24 PM
  3. How to count the number of times a date appears in a column
    By smellsgood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2010, 07:09 AM
  4. How to count the number of times a date appears in a column
    By smellsgood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2010, 06:35 AM
  5. Count how many times a number appears within a range of two numbers
    By Maristar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2009, 10:56 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