+ Reply to Thread
Results 1 to 5 of 5

Comparing the number "2015" to the year value of a date

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Comparing the number "2015" to the year value of a date

    Is there a way to compare the numerical value of a year (i.e. "2015") to the year of a date (10/12/15)?

    I have a column (col. C) on a different worksheet with a bunch of dates and I would like to sum the corresponding values in Col. V that match a particular year entered in cell (H2). Unfortunately, the year entered will only be the numerical representation of that year, not an actual date.

    Here is the formula I'm trying (I've highlighted the portion I need help with):

    =SUM(IF(AND($H$2=YEAR(Calcs!$C$5:$C$4244),MONTH(Calcs!$C$5:$C$4244)=2),Calcs!$V$5:$V$4244))

    Thanks in advance.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Comparing the number "2015" to the year value of a date

    Can you please attach your sample workbook?
    Cheers!
    Deep Dave

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Comparing the number "2015" to the year value of a date

    =SUMproduct(($H$2=YEAR(Calcs!$C$5:$C$4244))*(MONTH(Calcs!$C$5:$C$4244)=2)*Calcs!$V$5:$V$4244)
    Try this or below array formula (Shift+Ctrl+Enter)

    =SUM(IF(($H$2=YEAR(Calcs!$C$5:$C$4244))*(MONTH(Calcs!$C$5:$C$4244)=2),Calcs!$V$5:$V$4244))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Re: Comparing the number "2015" to the year value of a date

    Quote Originally Posted by nflsales View Post
    =SUM(IF(($H$2=YEAR(Calcs!$C$5:$C$4244))*(MONTH(Calcs!$C$5:$C$4244)=2),Calcs!$V$5:$V$4244))
    Thanks so much nflsales.

    I feel really stupid that it wasn't even a date issue but rather a formula syntax issue. That's why this forum is so valuable, I would of spent hours on this before I realized my error.

    Cheers.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Comparing the number "2015" to the year value of a date

    You are welcome and thanks for your 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: 1
    Last Post: 11-23-2014, 06:02 AM
  2. [SOLVED] Formula to Match a text year against a =text(date,"yyyy") and return a 1 value
    By john dalton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2014, 10:16 AM
  3. Date format of just "st" "nd" "rd" and "th" with text included
    By notrandom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2013, 05:45 PM
  4. Replies: 3
    Last Post: 01-17-2013, 07:20 PM
  5. Replies: 1
    Last Post: 09-15-2010, 01:55 AM

Tags for this Thread

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