A Total Count of All Records based on a Specific Year (i.e., 2014 or 2012) in Excel

  • What I am trying to do: I would like to search Excel 2010 Database records (containing various dates) and based on a specific year that I provide to Excel (i.e., 2014 or 2013); I want Excel to give me in return—a total count of all records from the year of (i.e., 2014 or 2013).

    Note: My dates are stored in the database as a Date Type(MM/DD/YYYY).

    Because, eventually I will combined this date-search operation (criteria) with multiple string-search operations (criteria); I am using the =COUNTIFS(date-search operation (criteria),…,…) formula (function) to implement this operation or procedure.

    To be frank, I’ve been trying for “a whole week, now” to troubleshoot my Excel 2010 =COUNTIFS(date-search operation (criteria),…,…) formula (function) implementation without success, needless to say…!

    And so, here is what I’ve done so far(below):

    =COUNTIFS(CourseTitle,"Mindfulness-Based Stress Reduction - Alumni Group",DateRN, If(Year(DateRN)=2013,TRUE,FALSE),QtrOfYrRN,"4th Quarter").

    As you can perhaps see, CourseTitle, DateRN and QtrOfYrRN are defined range names.

    At any rate, when I execute this command (formula) in Excel, I get as a result—something like (below):

    Criteria-range1…and Criteria1…results are expected results. Criteria-range2: DateRN = {41290, 41290…} and Criteria2: IF(YEAR(DateRN)=2013,TRUE,FALSE) = TRUE results are expected results, too. But, I find it very inconvenient, as well as difficult to perform comparison operations, etc., with this data storage type or Date format. Like I’ve indicated previously, I tried various approaches at trying to resolve this issue of “my current failure, to functionally or fully understand Excel’s ‘Date Field’ data type technicalities.” Anyhow, criteria-range3…and Criteria3…results are expected results, as well.

    For your information and to assist me at resolving this dilemma, the result of my implementation formula (below):

    =COUNTIFS(CourseTitle,"Mindfulness-Based Stress Reduction - Alumni Group",DateRN, If(Year(DateRN)=2013,TRUE,FALSE),QtrOfYrRN,"4th Quarter") = 0!” which is “Not True!

    “Please Help Me!”

    Thanks.

    Lamzie

  • Re: A Total Count of All Records based on a Specific Year (i.e., 2014 or 2012) in Exc


    Quote from Gizzmo;727450

    See the attached file. I have had to use a 'helper' column for Year and named this range DateRN, hope it helps.


    you can do the same thing without the helper column:


    =COUNTIFS(CourseTitle,"Mindfulness-Based Stress Reduction - Alumni Group",QtrOfYrRN,"4th Quarter",DateRN,">"&DATE(B2,1,1),DateRN,"<="&DATE(B2,12,31))

  • Re: A Total Count of All Records based on a Specific Year (i.e., 2014 or 2012) in Exc


    Quote from MrRedli;727452

    you can do the same thing without the helper column:


    =COUNTIFS(CourseTitle,"Mindfulness-Based Stress Reduction - Alumni Group",QtrOfYrRN,"4th Quarter",DateRN,">"&DATE(B2,1,1),DateRN,"<="&DATE(B2,12,31))


    Sweet, thanks for the improvement on my post


    Gizzmo

  • Re: A Total Count of All Records based on a Specific Year (i.e., 2014 or 2012) in Exc


    The simplest way is to use a PivotTable. Place the course titles in the Values and set the Values Field setting to Count. Putdates in Rows and Group dates by Years.


    As you are using one of the newer versions of Excel then also convert the data to a Table which will expand as you add data.

  • Re: A Total Count of All Records based on a Specific Year (i.e., 2014 or 2012) in Exc


    Amended file with 2 possible solutions (thanks to pivot as per royUK, amended formula as per MrRedli) and combination of dynamic names, helper column for year and data validation linked to unique list based on pivots.


    Hopefully Lamzie finds replies helpful.

  • Re: A Total Count of All Records based on a Specific Year (i.e., 2014 or 2012) in Exc


    Hello Gizzmo,


    Mission accomplished...!


    And, thanks a lot!



    By the way, I've used your first suggestion--that went something like this, below:


    "you can do the same thing without the helper column:


    =COUNTIFS(CourseTitle,"Mindfulness-Based Stress Reduction - Alumni Group",QtrOfYrRN,"4th Quarter",DateRN,">"&DATE(B2,1,1),DateRN,"<="&DATE(B2,12,31))."


    And: "lo and behold," it works!


    Of course, I had to substitute or adapt DATE(B2,1,1) and DATE(B2,12,31) with DATE(A509,1,1) and DATE(A509,12,31), according to my particular environment...!


    Again yes, it works, like a charm!


    Needless to say, I did not have to look at the attached file, etc.


    Thank you.


    Lamzie

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!