+ Reply to Thread
Results 1 to 5 of 5

countifs cell that contains specific year

  1. #1
    Forum Contributor
    Join Date
    06-07-2016
    Location
    Manila
    MS-Off Ver
    365
    Posts
    292

    countifs cell that contains specific year

    Hi All,

    I'm trying to do a countifs with a specific criteria

    I want to count all the entries of year 2019 and 2018.

    Please see attached, I've also placed my initial formula but it does not reflect the correct count

    Any help will do. thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: countifs cell that contains specific year

    You need to extract the year from the column A range which can be achieved by YEAR(range)
    But you can't put this in a COUNTIFs so use SUMPRODUCT instead

    in F2
    =SUMPRODUCT((YEAR($A$2:$A$1000)=F$1)*($B$2:$B$1000=$E2))
    and copy across and down the grid
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: countifs cell that contains specific year

    Another option...
    =COUNTIFS($B:$B,$E2,$A:$A,">="&DATE(F$1,1,1),$A:$A,"<="&DATE(F$1,11,31))
    copied down and across as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    06-07-2016
    Location
    Manila
    MS-Off Ver
    365
    Posts
    292

    Re: countifs cell that contains specific year

    Hi All, thanks! used FDibbins suggestion

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: countifs cell that contains specific year

    Happy to help and thanks for the feedback

+ 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. Replies: 2
    Last Post: 04-16-2019, 10:04 AM
  2. [SOLVED] Display text from specific cell based on result from a COUNTIFS
    By RookieExcele in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-27-2019, 09:58 AM
  3. [SOLVED] countifs to find the total count between this year and last year for series of data
    By maher2014 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2016, 02:39 AM
  4. [SOLVED] Countifs cell has date within month AND other cell on row is eual to specific criteria
    By justinr in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-09-2015, 12:42 PM
  5. Replies: 1
    Last Post: 01-08-2015, 09:49 PM
  6. [SOLVED] Count days for specific year falling within in multi-year date range
    By jslo2013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 05:58 PM
  7. Replies: 3
    Last Post: 03-12-2009, 09:54 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