+ Reply to Thread
Results 1 to 5 of 5

Countif function between two dates

  1. #1
    Registered User
    Join Date
    04-14-2008
    Posts
    5

    Countif function between two dates

    I have written the following countif formula, but need to add a second criteria and cannot get it to work.

    =COUNTIF(A1:A9, ">=1/1/2008")

    I would like to have an equation that can count the number of occurances between two dates (so I can count visits per quarter). For example, 1/1/08 to3/31/08. Once I get the number of visits per quarter I will multiply it by a dollar amount. However, each visit (column) has a different dollar amount associated with it and the visit windows in each column will all be at different times during the year.

    The equation above is all inclusive for anything greater than 1/1/08. Any help would be appreciated. If there is a better way to do this I am open to suggestions.

  2. #2
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    You can use SUMPRODUCT to do this, see

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    Ed
    _____________________________
    Always learning, but never enough!
    _____________________________

  3. #3
    Registered User
    Join Date
    04-14-2008
    Posts
    5

    Another suggestion?

    I looked at the information you sent, but it still doesnt help me with a date range. I may have overlooked it though. The example below is closest to the one that would help me, but my primary focus is to count the number of cells that have a date between 01/01/08 and 3/31/08. The SUM is not the main issue for my worksheet .

    Example 2: To count the number of sales in 3 locations of service since a given time period.

    Solution: In it's basic elements, this is a simple test. If the date to be tested against is in a cell it would be a simple
    =SUMPRODUCT((C5:C309>$A$1))*(H5:H309="A"))
    But this formula shows a technique to use embedded date strings that works, as far as I am aware, in all international versions of Excel.
    =SUMPRODUCT((C5:C309>(--("2004/05/31")))*(H5:H309="A"))

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  5. #5
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    For the kind of analysis you are doing, it's a good idea and design principle to enter the start and end dates in two cells and then refer to those in your formula. That way if you want to look at different periods you only have to change those 2 cells and not go back into the formula.

    So if you put your start and end dates in A1 and A2 and your visit dates are in C5 to C309

    Please Login or Register  to view this content.
    will count the number of occurances when visits took place on or between your dates

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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