+ Reply to Thread
Results 1 to 11 of 11

Count the times a variable year appears in a list of dates

  1. #1
    Registered User
    Join Date
    10-28-2012
    Location
    brussels
    MS-Off Ver
    Office 365
    Posts
    42

    Count the times a variable year appears in a list of dates

    Hi guys,

    i'm not able to construct a formula to solve this problem.

    Column B is filled from row 12 to 4000 with dates. (dd/mm/yyyy)
    Cell C2 contains the current year
    Please Login or Register  to view this content.
    Now I want a formula that counts how many times the year in cell C2 appears in the dates from column B.

    I've tried with countif and sumproduct and other things I found online but nothing seems to be working properly.

    Thanks in advance.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Count the times a variable year appears in a list of dates

    Try this:

    =SUMPRODUCT(--(YEAR(B12:B4000)=C2))

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Count the times a variable year appears in a list of dates

    or can try

    =COUNTIFS(B:B,">="&DATE(C2,1,1),B:B,"<="&DATE(C2,12,31))
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Registered User
    Join Date
    04-02-2018
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    11

    Re: Count the times a variable year appears in a list of dates

    Please Login or Register  to view this content.
    Or
    Please Login or Register  to view this content.
    Last edited by ruhalt; 04-02-2018 at 08:06 AM.

  5. #5
    Registered User
    Join Date
    10-28-2012
    Location
    brussels
    MS-Off Ver
    Office 365
    Posts
    42

    Re: Count the times a variable year appears in a list of dates

    this works, thanks !

    what does the "--" stand for?

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Count the times a variable year appears in a list of dates

    It basically convert TURE and FALSE into 1 and 0.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Count the times a variable year appears in a list of dates

    and does so like this
    YEAR(B12:B4000)=C2 returns TRUE/FALSE
    TRUE evaluates to 1 FALSE evaluiates to Zero
    To evaluate we do something arithmetical to the TRUE/FALSE

    -0 = 0
    --0 = 0
    -1= -1 - we do not want this
    --1 = 1

    TRUE becomes a 1 and FALSE becomes 0 (Zero)

    Multiplying by 1 (as in ruhalt's solution) achieves the same thing
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  8. #8
    Registered User
    Join Date
    10-28-2012
    Location
    brussels
    MS-Off Ver
    Office 365
    Posts
    42

    Re: Count the times a variable year appears in a list of dates

    And if I would like to add a condition to this conditional sum.
    Now it analyses if the year in the dates equals a year I demand. But what if I would like to count those whose date in column B equals the year but whose column C in the same time contains the word YES.

    So column B has dates and column C has "yes" and "no"'s. I want to count all those whose year matches the year I demand and in the same time have a Yes in column C.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Count the times a variable year appears in a list of dates

    For example use Phuocam's formula in post #2

    =SUMPRODUCT(--(YEAR(B12:B4000)=C2))

    and make it

    =SUMPRODUCT(--(YEAR(B12:B4000)=C2),--(C12:C4000="YES"))

    Try similarly with the other formulas.
    Dave

  10. #10
    Registered User
    Join Date
    10-28-2012
    Location
    brussels
    MS-Off Ver
    Office 365
    Posts
    42

    Re: Count the times a variable year appears in a list of dates

    thanks again!

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Count the times a variable year appears in a list of dates

    You're welcome. Thanks for the feedback and marking this thread Solved.

+ 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. [SOLVED] Count the times a month/year appear in a list of data
    By CliffyBiro in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-05-2018, 11:40 PM
  2. [SOLVED] Count then list how many times a Name appears in a Calandar week
    By Aeneren in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2015, 05:15 PM
  3. Count the number of times saturday appears between two dates
    By TGP in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2015, 07:17 AM
  4. Calculating the number of times a value appears based on Month and Year
    By achimbos in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2015, 06:58 PM
  5. Count and list dates that appears per person
    By Smally in forum Excel General
    Replies: 7
    Last Post: 09-01-2014, 10:52 AM
  6. [SOLVED] Count the number of times an A appears 5 or more times consecutively
    By CCook310 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-02-2013, 04:02 PM
  7. Replies: 3
    Last Post: 04-26-2013, 01:21 PM

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