+ Reply to Thread
Results 1 to 8 of 8

Count if the condition applies 2 or more times in a time span:

  1. #1
    Registered User
    Join Date
    09-18-2011
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    13

    Count if the condition applies 2 or more times in a time span:

    Column A has a list of items that I populate daily.
    Column B has dates for when the items were inserted.

    I am interested in identifying a specific item when it occurs 2 or more times in a time span (ie: 20 days).

    Manually, I sort the data by Column A, visually scan for when the item has occurred more than once, then check to see if the entries occurred in a certain time span.

    I am not sure how to begin writing formulas (or would I need VBA?) to do this automatically? I know how to use the countif formula. I think my biggest problem is that I do not know how to identify a range of dates. I know =Today() produces today's date. So =Today()-20 will produce the date 20 days ago. How can I use excel to search data between =Today() and Today() - 20?

    Or am I making this more complicated than it seems?

    Thanks,

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,309

    Re: Count if the condition applies 2 or more times in a time span:

    depending on how your data is setup....I would use a pivot table....just a thought
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count if the condition applies 2 or more times in a time span:

    Are you wanting to check for a specific item or any item(s)?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-18-2011
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Count if the condition applies 2 or more times in a time span:

    I have a pivot table set up, but I am unsure how to get the pivot table to identify when any item occurs more than once in the past 20 days. I attached a picture of what my data set up looks like.
    temp.png

  5. #5
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,309

    Re: Count if the condition applies 2 or more times in a time span:

    you could create a helper column that automatically subtracts that date from today and then filter on everything 20 days and below....

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Count if the condition applies 2 or more times in a time span:

    =countifs() would help

    assuming dates are in column A
    =COUNTIFS(A2:A104,">="&TODAY()-20,A2:A104,"<="&TODAY())

    then you can add the other criteria you need

    can you post a sample spreadsheet

    see the dates here counted
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  7. #7
    Registered User
    Join Date
    09-18-2011
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Count if the condition applies 2 or more times in a time span:

    I included a pivot table in the attached file. I have little experience with pivot tables and I am unsure how to get what I want accomplished.

    *How can I use etaf's solution to output/highlight the item?
    *Instead of having the formula search a 20 day span from Today's date, is it possible to have the formula search any 20 day span?
    Attached Files Attached Files

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Count if the condition applies 2 or more times in a time span:

    =COUNTIFS(A2:A104,">="&TODAY()-20,A2:A104,"<="&TODAY())

    you could just replace the today() with the date you want to use - maybe in a cell within the spreadsheet

+ 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. Copy-Paste Special - Transpose values if condition applies
    By Florinnn in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-01-2013, 05:35 AM
  2. [SOLVED] Count the number of times multiple condition are met
    By wishkey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2013, 07:02 PM
  3. how to disable a cell like user cannot input in it when the condition applies ?
    By shaal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2012, 03:47 AM
  4. Macros for inserting row if condition applies in different worksheet
    By Florinnn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2010, 10:54 AM
  5. macro to count data and produce graph for time span
    By shortman_alan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-02-2008, 08:03 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