+ Reply to Thread
Results 1 to 4 of 4

Need help with counting total number of dates that fall within a specific mon & yr

  1. #1
    Registered User
    Join Date
    07-26-2011
    Location
    california
    MS-Off Ver
    Excel 2007
    Posts
    43

    Need help with counting total number of dates that fall within a specific mon & yr

    I need help to count all the dates in one column that fall in a specific month and year. I have a summary table that needs to count the occurances for each month of the year.

    Dec Jan Feb
    CX Complete (F) 0 0 49 32

    The source data is in a separate tab under 1 column (S)

    Siterra - Forecast
    1/21/2013
    1/21/2013
    3/26/2012
    2/10/2012
    1/30/2012
    3/23/2012

    =COUNTIF('SF NetVis PostNTP'!$S:$S,

    Is what I started with but can't figure out the rest....

    thanks

    Hitman

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Need help with counting total number of dates that fall within a specific mon & y

    You can use COUNTIF or COUNTIFS, like;

    =COUNTIF('SF NetVis PostNTP'!$S:$S,">="&Start_Date)-COUNTIF('SF NetVis PostNTP'!$S:$S,">"&End_Date)

    =COUNTIFS('SF NetVis PostNTP'!$S:$S,">="&Start_Date,'SF NetVis PostNTP'!$S:$S,"<="&End_Date)

    If you just have Start_Date, replace End_Date with EOMONTH(Start_Date,0)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    07-26-2011
    Location
    california
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Need help with counting total number of dates that fall within a specific mon & y

    Haseeb, thanks that helped...I was close. Also now for the next step. I have the same need to count the dates from another data column and add them up for a total. One source is the SF NetVis tab and the other is the SB Netvis tab. I tried to just add in the same range sequence to the end but its not working.

    I tried the following:

    =COUNTIFS('SF NetVis PostNTP'!$CS:$CS,">=12/1/2011",'SF NetVis PostNTP'!$CS:$CS,"<=12/31/2011")+('SB NetVis PostNTP'!$CS:$CS,">=12/1/2011",'SB NetVis PostNTP'!$CS:$CS,"<=12/31/2011")

    =COUNTIFS('SF NetVis PostNTP'!$CS:$CS,">=12/1/2011",'SF NetVis PostNTP'!$CS:$CS,"<=12/31/2011",'SB NetVis PostNTP'!$CS:$CS,">=12/1/2011",'SB NetVis PostNTP'!$CS:$CS,"<=12/31/2011")

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Need help with counting total number of dates that fall within a specific mon & y

    You can't use COUNTIFS with + ranges, use 2 COUNTIFS, like

    =COUNTIFS('SF NetVis PostNTP'!$CS:$CS,">=12/1/2011",'SF NetVis PostNTP'!$CS:$CS,"<1/1/2012")+COUNTIFS('SB NetVis PostNTP'!$CS:$CS,">=12/1/2011",'SB NetVis PostNTP'!$CS:$CS,"<1/1/2012")

    Note: I changed the 2nd condition to "<1/1/2012", because you were said, you have date with time. If you use "<=12/31/2011" this wouldn't count any date, if there is 12/31/2011 with time. like, 12/31/2011 11:00:25 AM etc...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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