+ Reply to Thread
Results 1 to 4 of 4

Counting specific dates and sub values

  1. #1
    Registered User
    Join Date
    09-15-2006
    Posts
    3

    Counting specific dates and sub values

    I have a spreadsheet in 2007 that contains 3 columns of data that need to be analyzed. The first column is a date, the second column is either an x or blank, the third column is either a yes, no, or blank.
    What I need to perform is the following:

    1) Review the first column to see if it has a value that is in the date range of the year 2013, then sum up all of the 2013 values.
    2) Review the second column to see if it has an x, and only sum up the x value against the first row that has a value of 2013.
    3) Review the third column to see if it has a yes, and only sum up the yes value against the first row that has a value of 2013.

    Im sure the formula for my last two requests will be very similar in nature, but those are the ones Im struggling with the most. I have been able to accomplish the first operation fairly successfully with the following formula:

    =SUMPRODUCT(--(YEAR($A$2:$A$3000)=2013))

    But incorporating this into the next two formulas has not worked in any of my attempts.

    If anyone has any suggestions I would greatly appreciate them. Also, if there is an easier way to accomplish the first task, Im all ears.

    Many thanks.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Counting specific dates and sub values

    Perhaps

    =SUMPRODUCT((YEAR(A2:A3000)=2013)*(B2:B3000="X")*(C2:C3000="YES")*(C2:C3000))

    UNTESTED, as i don't have a sample workbook
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,460

    Re: Counting specific dates and sub values

    Hi Leroy, welcom back the forum.
    In fact, your SUMPRODUCT gives a count of cells in A column where is in the year 2013. I could not find any values need toa be added up in any column 1 or 2 or 3? Where is the column that contains values?

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Counting specific dates and sub values

    Hi Leroy221

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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