+ Reply to Thread
Results 1 to 5 of 5

Count cells with a specific year

  1. #1
    Forum Contributor
    Join Date
    10-31-2006
    Location
    Oslo, Norway
    MS-Off Ver
    MS 365 Business
    Posts
    282

    Count cells with a specific year

    I have a table with cells with date-format (d-mmm-yyyy).
    I want to find the number of celles which refers to a specific year using formula COUNTIF.
    Can somebody help me with the formula ?
    Last edited by Saturn; 04-20-2011 at 12:25 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count cells with a specific year

    If you have to use COUNTIF, then perhaps something like:

    =COUNTIF(A:A,">="&DATE(2011,1,1))-COUNTIF(A:A,">"&DATE(2011,12,31))

    or you can use Sumproduct:

    =Sumproduct(--(Year(A1:A100)=2011))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    10-31-2006
    Location
    Oslo, Norway
    MS-Off Ver
    MS 365 Business
    Posts
    282

    Re: Count cells with a specific year

    NBVC: great! Thank you, once again!

  4. #4
    Forum Contributor
    Join Date
    10-31-2006
    Location
    Oslo, Norway
    MS-Off Ver
    MS 365 Business
    Posts
    282

    Re: Count cells with a specific year

    I have reopened this thread.

    I have two questions:
    1: what does the "--" in ..(--(YEAR.. stand for?
    2: I want to add sum of kilometer for each year. Please see the attachment. I have tried SUMPRODUCT and SUMIFS without success.

    Saturn
    Attached Files Attached Files

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count cells with a specific year

    The -- is a double unary. It is used to coerce TRUE and FALSE results from the conditional checks to 1's and 0's, respectively, so that the match can be performed.

    Try:

    =SUMPRODUCT(--(YEAR($B$4:$B$16)=F4),$C$4:$C$16)

+ 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