+ Reply to Thread
Results 1 to 8 of 8

Highlight cells if combined exceeds certain threshold set in another table

  1. #1
    Registered User
    Join Date
    11-30-2020
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    8

    Highlight cells if combined exceeds certain threshold set in another table

    Hi There

    I need help to highlight cells in Term1 -3 of Table1 separately if the count of value in those cells exceeds the max total set in Table 2. I have attached the tables here. I tried using conditional formatting with the following formula but it didn't seem to work.

    For example, if the formula works in Term1, ENT should be highlighted since it appears more than 3 times but GP and Surgery not since they are equal the Max total.

    =highlight B3:B13
    =countif($B$3:$B$13,$G$3:$G$5)>$H$3:$H$5

    Thanking in advance for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Highlight cells if combined exceeds certain threshold set in another table

    Hi and welcome

    Try this as your CF formula

    =SUMPRODUCT(--(B3=$B$3:$B$13))>VLOOKUP(B3,$G$3:$H$5,2)
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,199

    Re: Highlight cells if combined exceeds certain threshold set in another table

    CF formula for cell B3:

    =COUNTIF(B$3:B$13,B3)>VLOOKUP(B3,$G$3:$H$5,2,0)

    Apply for whole table

  4. #4
    Registered User
    Join Date
    11-30-2020
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    8

    Re: Highlight cells if combined exceeds certain threshold set in another table

    Thanks for your help!

    It doesn't seem to work quite right though. The value of b3:d13 in Table1 will need to keep changing as we use the table to try to get the right number of students in the right unit. I also need the formula to not just highlight 1 type of unit (ENT) but all of them (ENT, Surgery, GP) if they exceed the max total per term in Table2.

    Thanks for replying!

  5. #5
    Registered User
    Join Date
    11-30-2020
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    8

    Re: Highlight cells if combined exceeds certain threshold set in another table

    Quote Originally Posted by bebo021999 View Post
    CF formula for cell B3:

    =COUNTIF(B$3:B$13,B3)>VLOOKUP(B3,$G$3:$H$5,2,0)

    Apply for whole table
    Wow! It seems to work. I don't understand how but it does. Thanks so much for your help!

  6. #6
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Highlight cells if combined exceeds certain threshold set in another table

    You said you couldn't get it working. Here is a working example

  7. #7
    Registered User
    Join Date
    11-30-2020
    Location
    Melbourne
    MS-Off Ver
    2016
    Posts
    8

    Re: Highlight cells if combined exceeds certain threshold set in another table

    Quote Originally Posted by Crooza View Post
    You said you couldn't get it working. Here is a working example
    Ah it does work now beautifully, thanks again for your help. With this formula, the CF needs to be applied one column at a time. I can't just apply it to the whole table, am I right?

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Highlight cells if combined exceeds certain threshold set in another table

    Yes it will work across the whole range but you'll need to make one small change - remove the $ from in front of the B

    old formula
    =SUMPRODUCT(--(B3=$B$3:$B$13))>VLOOKUP(B3,$G$3:$H$5,2)

    new formula

    =SUMPRODUCT(--(B3=B$3:B$13))>VLOOKUP(B3,$G$3:$H$5,2,0)

+ 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] Highlight hh:mm:ss when a threshold is exceeded
    By ChrisH0916 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2020, 06:26 PM
  2. Conditional Formatting when one date exceeds another by a threshold amount
    By jtfolk22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2020, 04:22 PM
  3. Crosstab table to pivot table to plain table exceeds Excel row limit
    By jjsilva in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-30-2017, 11:43 PM
  4. highlight if amount exceeds in column
    By anuwers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2016, 05:05 AM
  5. MsgBox when cell value in column exceeds a threshold.
    By Stew1234 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2014, 02:41 PM
  6. [SOLVED] SUM number values according to days of week and highlight red If exceeds ?
    By Lukael in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-12-2014, 09:35 AM
  7. Counting number of times a maximum exceeds a threshold on a certain frequency
    By varsakov in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2013, 02:21 PM

Tags for this Thread

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