+ Reply to Thread
Results 1 to 4 of 4

=COUNTIFS statement

  1. #1
    Registered User
    Join Date
    04-09-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    7

    =COUNTIFS statement

    I am running into a small problem getting a COUNTIFS formula to work. I need the formula to look at a different tab that contains the data. I need to count two different pieces of cirteria shown below.
    Criteria 1 - project name
    Criteria 2 - count # of cells (by date range) that relate to the project by month/year

    If I count the blank cells (for the date range) it works fine. What I cannot get to work is getting the date range piece.

    here is my current formula (to count blank date ranges)

    =COUNTIFS(Projects2009toCurrent!L:L,"*Project 2.0*",Projects2009toCurrent!Q:Q,"")

    I know at the end of the formula where the "" is, I need to include a date range. The date range I am trying to use is 10/1/2013 to 10/31/2013.

    Any help is appreciated.

    Glen

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: =COUNTIFS statement

    The following formula should work:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    04-09-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: =COUNTIFS statement

    Thanks - I made a small change and it is working.

    =COUNTIFS(Projects2009tocurrent!L:L,"*Project 2.0*",Projects2009tocurrent!Q:Q,">="&"October 1, 2013",Projects2009tocurrent!Q:Q,"<"&"October 31, 2013")

    is now =COUNTIFS(Projects2009tocurrent!L:L,"*Project 2.0*",Projects2009tocurrent!Q:Q,">="&"10/1/2013",Projects2009tocurrent!Q:Q,"<"&"10/31/2013")

    One follow up question. What I would like to do next is to copy this formula to the cells below and have the dates change to the next month. So 10/1/2013 becomes 11/1/2013 and so on. Once I get to 2014, the year will flip 2014. Is there a way to do this?

  4. #4
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: =COUNTIFS statement

    Odd. I tested mine in excel and it worked. oh well.

    As for having the dates change, you would need to use helper column (have the two dates in two columns, for Example columns S and T, and then use the following formulas in S3 and T3 respectively):

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

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


    Change the countifs function to reference these cells now:

    =COUNTIFS(Projects2009tocurrent!L:L,"*Project 2.0*",Projects2009tocurrent!Q:Q,">="&S2,Projects2009tocurrent!Q:Q,"<"&T2)

+ 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] Problem in COUNTIFS statement
    By abduljaleel.mca in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2013, 02:32 AM
  2. Or function within a countifs statement
    By transitsolutions in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2013, 02:38 PM
  3. [SOLVED] Countifs statement with a Vlookup as well.
    By jayres14 in forum Excel General
    Replies: 3
    Last Post: 07-21-2012, 07:24 AM
  4. Countifs Statement
    By turpink in forum Excel General
    Replies: 1
    Last Post: 09-12-2011, 01:01 PM
  5. Too long countifs statement
    By kcasey1318 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2010, 10:39 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