+ Reply to Thread
Results 1 to 5 of 5

IF multiple criteria

  1. #1
    Registered User
    Join Date
    08-08-2007
    MS-Off Ver
    Excel 2010
    Posts
    53

    IF multiple criteria

    Hi, trying to figure out the following.

    Column A has multiple instances of the same name.
    column B may have 'after' or 'prior'

    I want a result where the same name appears in column A but only if in column B that name has both 'after' AND 'prior' in seperate cells for the same name in column A.
    Last edited by scabertrain; 11-28-2013 at 12:57 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: IF multiple criteria

    If you generate a unique list of values from column A and place it in column F (remove duplicates on data ribbon), the following formula in G1 and then copied down will show if the value in A has both after and prior.

    =IF(IF(COUNTIFS(A:A,F1,B:B,"after")>0,1,0)+IF(COUNTIFS(A:A,F1,B:B,"prior")>0,1,0) = 2,TRUE,FALSE)
    Martin

  3. #3
    Registered User
    Join Date
    08-08-2007
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: IF multiple criteria

    That worked! Thanks!

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

    Re: IF multiple criteria

    shorter...
    Life's a spreadsheet, Excel!
    Say thanks, Click *

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

    Re: IF multiple criteria

    shorter...

    =AND(COUNTIFS(A:A,F1,B:B,"after"),COUNTIFS(A:A,F1,B:B,"prior"))

+ 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] UDF for sum of multiple criteria in multiple columns and single criteria in multiple colum
    By Ganesh7299 in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 11-22-2013, 04:26 AM
  2. Count statement wtih multiple criteria only showing results for one criteria
    By uhlabomber in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2013, 02:47 PM
  3. [SOLVED] create drop down box to select multiple criteria and link macro to each criteria
    By anand_erin in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-03-2012, 06:35 PM
  4. Replies: 2
    Last Post: 10-05-2011, 12:43 PM
  5. MACROS: Sort by multiple criteria - Create tabs based off of criteria
    By svineyard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2009, 10:48 AM

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