+ Reply to Thread
Results 1 to 3 of 3

Conditional format based on multiple criteria

  1. #1
    Registered User
    Join Date
    11-23-2014
    Location
    Wichita
    MS-Off Ver
    2007
    Posts
    17

    Conditional format based on multiple criteria

    I have a conditional format in a cell (b6) that if that value appears elsewhere (sheet2,colum b), the format is applied.
    =countif(sheet2!b1:b10,sheet1!b6)>=1
    I would like it to only be applied if the exact value appears on sheet 2,colum b, and If there is a value next to it on sheet 2 column c.
    How do I add the second condition?

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Conditional format based on multiple criteria

    I would probably go with =MATCH() rather than countif() (but thats my preference, I dont think there is a logical/technical reason for that)

    You could then probably put that into an INDEX() to test for something in the next cell.

    If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    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
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    963

    Re: Conditional format based on multiple criteria

    Add an S to the Countif statement to make it multiple conditions. Personally if and when I use Sumif or Countif... it is Sumifs or Countifs, really no need to limit yourself so make it a habit to use those.

    Now that is really a side note since you are looking through 2 different ranges for that value. That is, if I read what you put right. The answer is in your question!
    I would like it to only be applied if the exact value appears on sheet 2,colum b, and If there is a value next to it on sheet 2 column c.
    Use an AND statement telling it to find a value is just to say that the cell value in that range is >0 in a count if you wrap up your mathematical qualifiers with quotes and throw an & symbol to join the criteria, so ">"&0

    =AND(Countifs(sheet2!b1:b10,sheet1!b6)>0,Countifs(sheet2!C1:C10,">"&0)>0)

    That should do it...
    -If you think you are done, Start over - ELeGault

+ 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] Conditional format for duplicate values based on criteria in a different cell
    By excelheaven in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2012, 11:34 AM
  2. Replies: 4
    Last Post: 12-17-2011, 06:11 PM
  3. Conditional Format Multiple Criteria VBA
    By SamsamF in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2011, 08:45 AM
  4. Conditional format based on 4 criteria??
    By leem in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-02-2010, 11:10 AM
  5. [SOLVED] Conditional Format based on multiple criteria
    By maw230 in forum Excel General
    Replies: 9
    Last Post: 02-16-2010, 06:24 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