+ Reply to Thread
Results 1 to 3 of 3

Counting a value based on a range of dates

  1. #1
    Registered User
    Join Date
    01-07-2009
    Location
    Derby, England
    MS-Off Ver
    Excel 2000
    Posts
    3

    Counting a value based on a range of dates

    Hi,

    I'm having difficulty with counting a number of values based on a range of dates. (See example spreadsheet attached)
    In column A, I have opening dates for requests, e.g. 12-Jun-2009, in the 'Date' format.
    In column B, I have categories for each request, (1, 2 or 3) ('General' format).

    I want to count all the requests opened for each category in a given calendar month.

    For example, count all the Category 1 requests opened in June 2009.
    Using the Countif function, I can count the total number of requests (i.e. all categories) opened in June 2009, and I can also count all of the requests opened for a given category over the whole period.

    However, I cannot find a way of combining the two conditions (although it is possibly a fairly simple solution).

    Any advice appreciated!

    Thanks,
    Sean Donohoe.
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,445

    Re: Counting a value based on a range of dates

    Hi,

    How about this for June 2009 Category 1

    =SUMPRODUCT((YEAR(A2:A11)=2009)*(MONTH(A2:A11)=6)*(B2:B11=1))

    and modified to suit the other conditions
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    01-07-2009
    Location
    Derby, England
    MS-Off Ver
    Excel 2000
    Posts
    3

    Re: Counting a value based on a range of dates

    Hi Sweep,

    Yes that works well.
    Thank you very much for the quick and helpful reply!

    Sean

+ 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