+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting based on sum of values with the same entries in another column

  1. #1
    Registered User
    Join Date
    12-30-2021
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    4

    Question Conditional formatting based on sum of values with the same entries in another column

    Hi,

    I'm not sure how to even look for this exact solution, so apologies if this is was solved before, however, I need a conditional formatting based on a formula that would look for the same values in one column of a table (i.e. name of the employee) and then apply formatting to cells in the same rows but another column based on the sum of values in those cells, if it does not meet specified criteria.

    The problem is I would like to be as simple as possible (so no VBA or queries) and work on Excel 2016 (so no filter / unique formulas) so the most users can actually make a use of it.
    The names of employees are also subject to change, so I cannot make a formula for each known entry separately.

    On the example of the attached spreadsheet:
    The conditional formatting would highlight any cells in specific column (each day with separate formatting) if the sum of values in that column for "norm" entry in column F is less than 8 in all rows with the same name of the employee (column B).

    E.g. 1. John Smith have 4 hours of normal time working with Team 1 and 3 hours of normal time working with Team 2 on the 3rd of January. I would like to have both those values highlighted as their sum is less than 8.
    E.g. 2. Michael Johnson have 4 hours of normal time working with Team 1 and 4 hours of normal time working with Team 2 on the 3rd of January. Additionally he did 2 hours overtime ("OT") with Team 1 on that day. I want the "4"s to not be highlighted as he worked total of 8 hours normal time on that day, which is expected.

    Any new name can be added in the B column in any number of rows in the table and they are not always sorted but the conditional formatting looks for any rows with the same employee name in the column B and sums up all values for specific day.

    I hope I made the request clear enough.
    Attached Files Attached Files
    Last edited by Mikaill; 12-31-2021 at 03:53 AM. Reason: Solved

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: Conditional formatting based on sum of values with the same entries in another column

    Try

    CF formula

    =AND($F6="Norm",SUMPRODUCT((G$6:G$10)*(G$6:G$10<>0)*($B$6:$B$10=$B6)*($F$6:$F$10="Norm")*($G$5:$AK$5=G$5))>0,SUMPRODUCT((G$6:G$10)*(G$6:G$10<>0)*($B$6:$B$10=$B6)*($F$6:$F$10="Norm")*($G$5:$AK$5=G$5))<8)
    Attached Files Attached Files
    Last edited by JohnTopley; 12-30-2021 at 10:09 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    12-30-2021
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    4

    Thumbs up Re: Conditional formatting based on sum of values with the same entries in another column

    Hi,

    On the first look this seems to be doing what I needed, thanks a lot!
    Best wishes for the new year.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: Conditional formatting based on sum of values with the same entries in another column

    Thank you for the feedback and best wishes for the New Year to you.

  5. #5
    Registered User
    Join Date
    12-30-2021
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    4

    Re: Conditional formatting based on sum of values with the same entries in another column

    Hey John, one more request after all - would it be possible to make it work when values other than numbers are entered to the column?
    It seems to stop working when I put a comment like "Absent" instead of number.
    Last edited by AliGW; 12-31-2021 at 06:47 AM. Reason: PLEASE don't quote unnecessarily!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,389

    Re: Conditional formatting based on sum of values with the same entries in another column

    Try

    =AND(SUMIFS(G$6:G$13,$B$6:$B$13,$B6,$F$6:$F$13,"Norm")>0,SUMIFS(G$6:G$13,$B$6:$B$13,$B6,$F$6:$F$13,"Norm")<8)

    and ensure OT CF is top of the list (see attached)
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-30-2021
    Location
    Warsaw, Poland
    MS-Off Ver
    2016
    Posts
    4

    Re: Conditional formatting based on sum of values with the same entries in another column

    This seems to do the trick, thanks a lot again!

+ 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. Conditional formatting based on old C-column values.
    By ghjk22 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-14-2019, 12:25 PM
  2. Replies: 8
    Last Post: 04-28-2019, 08:51 AM
  3. Conditional formatting gradient based on frequency of text entries
    By geobeck in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-09-2018, 01:52 PM
  4. Replies: 6
    Last Post: 05-25-2018, 03:26 AM
  5. [SOLVED] Conditional Formatting of one cell based on values in a column
    By hammer68 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-23-2016, 06:12 AM
  6. [SOLVED] Conditional Formatting Rows based on Unique Values in Column
    By drjones5 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2013, 04:27 PM
  7. conditional formatting based on a series of column text values
    By stewphil in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 03:34 AM

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