+ Reply to Thread
Results 1 to 13 of 13

Count Cells if they Contain a Month

  1. #1
    Registered User
    Join Date
    07-06-2007
    Posts
    70

    Count Cells if they Contain a Month

    Hi all,

    I want to be able to count cells if they contain a certain month.

    For example, say i had the following date and times

    A
    10/01/2009 09:30
    10/01/2009 09:30
    10/02/2009 09:30
    10/03/2009 09:30
    10/03/2009 09:30
    10/09/2009 09:30
    10/09/2009 09:30
    10/09/2009 09:30
    10/12/2009 09:30

    How would i count how many are in the month of February?

    Thanks in advance!
    Last edited by VBA Noob; 02-10-2009 at 09:39 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count Cells if they Contain a Month

    Is the year relevant - ie distinguish between Feb 08 and Feb 09 ?
    Last edited by DonkeyOte; 02-10-2009 at 05:58 AM.

  3. #3
    Registered User
    Join Date
    07-06-2007
    Posts
    70

    Re: Count Cells if they Contain a Month

    It the moment no but perhaps further down the line it will be!

    Thanks!

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Cardiff
    MS-Off Ver
    2003
    Posts
    123

    Re: Count Cells if they Contain a Month

    You can use this:

    =SUMPRODUCT((A1:A4>39844)*(A1:A4<39873))

    This will do feb 09
    But if you want to find another month all you have to do is replace the numbes in bold eg for jan 09 > 39813 < 39845

    You can find these numbers by typing in the date for the day before and the day after the range that you want and then changing the format to number. You can do this for any date but make sure you put the year in as well.

    Then change the format for the cell with the formula to number, other wise you will get a date

    Hope this helps

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count Cells if they Contain a Month

    Examples:

    Year not relevant: Cell C1 contains Date 1st Feb 2009

    =SUMPRODUCT(--(MONTH(A1:A10)=MONTH(C1)))

    Year relevant: Cell C1 contains Date 1st Feb 2009

    =SUMPRODUCT(--(INT(A1:A10)-DAY(A1:A10)+1=C1))

  6. #6
    Registered User
    Join Date
    07-06-2007
    Posts
    70

    Re: Count Cells if they Contain a Month

    Hi all,

    I'm struggling to get either of these options to work?

    Any further help would be appreciated!

    Cheers

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count Cells if they Contain a Month

    The formulae provided would work with the sample data (assuming for my ex's you did enter the Feb 1 date into C1) -- if you can't get the functions to work with your real data you will need to provide a sample else we're all just going to be guessing I'm afraid.

  8. #8
    Registered User
    Join Date
    07-06-2007
    Posts
    70

    Re: Count Cells if they Contain a Month

    Apologies,

    I have now got it working but it appears to be counting blanks.

    What purpose do the "--" have at the start?

    Thank you for your continued assistance!

    Sample attached!

    Chris
    Attached Files Attached Files

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count Cells if they Contain a Month

    Use the other approach offered, namely:

    Please Login or Register  to view this content.
    MONTH will coerce a blank to 0 and day 0 is 0 Jan 1900 (ignoring 1904 Date System)... ie MONTH 1.

    You would need to use an additional qualifier to exclude the blanks, eg:

    Please Login or Register  to view this content.
    -- is double unary operator.. used to coerce Booleans to Integer.
    Run a search on this forum for more explanations... I've been asked three times today already ;-)

    Also for all things Sumproduct review Bob's site:
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    (the above was offline for a while but I think is now back up again ?)
    Last edited by DonkeyOte; 02-10-2009 at 08:33 AM. Reason: missing parentheses

  10. #10
    Registered User
    Join Date
    10-31-2008
    Location
    Philippines
    Posts
    47

    Re: Count Cells if they Contain a Month

    Hi CJConnor,

    Try this formula, it is entered as an array formula (ctrl+shift+enter).

    =SUM(IF($A$1:$A$15>=D3,IF($A$1:$A$15<=EOMONTH(D3,0),1,0),0))
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-01-2008
    Posts
    16

    Re: Count Cells if they Contain a Month

    What about COUNTIF

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count Cells if they Contain a Month

    Quote Originally Posted by emanon132501 View Post
    Try this formula, it is entered as an array formula (ctrl+shift+enter).
    =SUM(IF($A$1:$A$15>=D3,IF($A$1:$A$15<=EOMONTH(D3,0),1,0),0))
    2 points (other than the solution offered already works)

    a) a CSE array is regarded as slower than a Sumproduct

    b) EOMONTH requires AnalysisToolPak be activated - please always be sure to inform OP of this if using ATP function.

    EDIT - 3 points...

    3rd point... if you insisted on using a CSE array could simplify somewhat to:

    Please Login or Register  to view this content.
    The * will coerce the Booleans, such that TRUE * TRUE = 1 ... no need for IF approach in this instance.
    Last edited by DonkeyOte; 02-10-2009 at 10:01 AM.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Count Cells if they Contain a Month

    Quote Originally Posted by christopher2222 View Post
    What about COUNTIF
    Perfectly valid you would however need to use 2 of course, ie

    Please Login or Register  to view this content.

+ 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