+ Reply to Thread
Results 1 to 5 of 5

Formula to count if 2 conditions occur

  1. #1
    Registered User
    Join Date
    06-16-2016
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    3

    Formula to count if 2 conditions occur

    I have a sheet with 2 columns of dates "Date opened" and "Date closed"

    not all lines have a date closed entry.

    I'm trying to count how many cases have been opened longer than 18 months and are still open.

    =COUNTIF('Tracker'!F6:F1582,"<"&TODAY()-"545")

    The above gives me the count of cases with date opened older than 545 days ago (18 months approx).
    If I use filters I can see that there are 8 cases of which 3 are still open (ie don't have a date in the closed column)

    How do I get the formula to count the blanks or subtract the non-blanks from the new count?

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Formula to count if 2 conditions occur

    Hello Stufft,

    Welcome to the forum

    Available from Excel 2007 onward is COUNTIFS, where it's COUNTIF with more condition to it, for example

    =COUNTIFS('Tracker'!F6:F1582, "<"&TODAY()-"545", Range 2, "<>""")

    Where it will count the number of date opened older than 545 days ago, and the date in Range 2 is not blank.

    Available for all Excel version is SUMPRODUCT, you can use it like a COUNTIFS on any version
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Registered User
    Join Date
    06-16-2016
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    3

    Re: Formula to count if 2 conditions occur

    many thanks

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to count if 2 conditions occur

    Quote Originally Posted by Lemice View Post
    =COUNTIFS('Tracker'!F6:F1582, "<"&TODAY()-"545", Range 2, "<>""")
    No need to quote 545

    Is not blank can be expressed as <>.

    =COUNTIFS('Tracker'!F6:F1582,"<"&TODAY()-545,Range2,"<>")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    06-16-2016
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    3

    Re: Formula to count if 2 conditions occur

    FYI, the SUMPRODUCT function works if there's another column counting the number of weeks.

+ 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. How can I have an email automatically sent to me if two conditions occur?
    By ccb12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2016, 04:54 AM
  2. [SOLVED] Need the Count of cells only if they occur consecutively
    By bahanley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-12-2015, 06:09 PM
  3. program a formula to count number of times event occur before a specific time.
    By yinxzon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 04:15 AM
  4. Replies: 5
    Last Post: 07-23-2014, 12:46 PM
  5. Count when values occur in different arrays
    By smileyc in forum Excel General
    Replies: 6
    Last Post: 02-26-2009, 07:56 AM
  6. Replies: 6
    Last Post: 02-14-2008, 09:58 AM
  7. Return Value If Two Conditions occur
    By Hatman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-30-2006, 04:35 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