+ Reply to Thread
Results 1 to 5 of 5

Advanced Lookup to count cells in a column that meet criteria

  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    50

    Advanced Lookup to count cells in a column that meet criteria

    Hi all,

    In the attached spreadsheet there are two tables - table 1(left table) and table 2 (right table)

    Table 1 uses the following conditional formatting formula in column C: =AND(ISBLANK(C1),NOT(ISBLANK(D1))).

    In table 2, I want to count the number of rows in table 1 that have been highlighted by the above conditional formatting formula - Hopefully this is made clear in the attachment but let me know if I can further explain.


    Many, many thanks for helping to solve this!
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Advanced Lookup to count cells in a column that meet criteria

    Try

    =COUNTIFS(Table_Default__CART_MODIFIED_COSL_REP5_COR1[Projected Date],"",Table_Default__CART_MODIFIED_COSL_REP5_COR1[Actual Date],"<>")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    04-16-2013
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Advanced Lookup to count cells in a column that meet criteria

    Thanks, Ace_XL, for your help - It looks like that formula returned the total number of Sites that met the criteria, which is helpful but I was hoping to count the number of instances that each project number meets the criteria - e.g. Cell G3 would return 3, G4 would return 2, etc. Does that make sense?

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Advanced Lookup to count cells in a column that meet criteria

    In that case.. in G2

    =COUNTIFS(Table_Default__CART_MODIFIED_COSL_REP5_COR1[Project Number],F2,Table_Default__CART_MODIFIED_COSL_REP5_COR1[Projected Date],"",Table_Default__CART_MODIFIED_COSL_REP5_COR1[Actual Date],"<>")

    Copy down

  5. #5
    Registered User
    Join Date
    04-16-2013
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Advanced Lookup to count cells in a column that meet criteria

    Both of these formulas will be super helpful! I really appreciate the help, you nailed it!

+ 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] How to count cells that meet two criteria. Please Help!
    By Doolie01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2013, 02:07 PM
  2. [SOLVED] Count Unique Values in Column A that meet criteria in Column B
    By ashakespeare in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2012, 07:33 PM
  3. count cells that meet criteria
    By bmunoz64 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-01-2009, 08:19 AM
  4. Count cells only if three in a row meet criteria
    By htollefson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-29-2007, 07:47 AM
  5. count non blank cells which meet criteria in another column
    By cmarsh5035 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2005, 12:45 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